This tool is very good to fill in data gaps (if any) based on logic.
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
HI @GrantSmith , Rank-Window functions dont work like the MULTI-ROW function works. Let me give you an example, we have following rows,
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 :
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.
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.
SELECT `Dept`,`Value`, COALESCE(`Value`, IF(`tbl`.`Dept`[email protected], @[email protected], @[email protected])) 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
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.