Skip to content

    • Magic ETL

    Magic ETL

    Is there any way we could replicate Multi-Row formula tile of Alteryx tool in Domo?

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Answers

    • Coach

      Hi @LoveBindal

      Have you looked into Window functions? You can do it in either an ETL using the Rank and Window tile (https://domohelp.domo.com/hc/en-us/articles/360042922814-Magic-ETL-Tiles-Rank-and-Window) or you can use window functions within beast modes. Specifically utilizing LAG and LEAD will allow you to get the N prior row or the X next row. I've done a writeup of how to use LAG and/or LEAD for a rolling 3 day average here: https://dojo.domo.com/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages#latest

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Domo Employee

      HI @GrantSmith , Rank-Window functions dont work like the MULTI-ROW function works. Let me give you an example, we have following rows,

      image.png

      Here we want value = value(row-1)+1 where row= row-1 else value(row-1) - 0.1

      This should bring incremental values in MULTI-ROW but doesnt in our rank-window or windowed function.

      Expected output should be :

      image.png

      if we use our tools in domo, then we would get 1,2,3,3,3,2.9,1 resp which is wrong.

      Please let me know if you can help me with this.


      Thanks🐼

    • Coach

      So this isn't easily possible however you could implement a mysql data flow using some creative hackery using variables. I was able to write up an example using your sample data you had provided to get the results you expected.

      1. SELECT `Dept`,`Value`, COALESCE(`Value`, IF(`tbl`.`Dept`=@pred, @prev+@offset, @offset+@prev-1.1)) as new_value, @offset, CASE WHEN `Value` IS NOT NULL THEN @prev:=`Value` END prev, @pred:=`Dept` pred, @offset:=1+case when `Value` IS NULL THEN @offset ELSE 0 END new_offset
      2. FROM `tbl`, (SELECT @pred := null, @prev := null, @offset:=0) var_init

      Essentially it's keeping track of how much offset to add if the current value is null. It's not a perfect working version but gives you an idea. If a new department starts with nulls it won't handle that scenario properly for the 2nd null row in a department.

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories