Best Of

Try clicking on Run Preview and see if that helps the ETL recognize what columns actually exist. You can also use the expanded formula editor and double-click the column in the list to add it to the formula editor to ensure there are no typos.

You can utilize a rank and window tile within Magic ETL to do this with the LAG function.

Partition by SNO and MMODEL and Order By your date field with a lag value of 1. This will give you the prior record's date where you can then feed it into a formula tile or a date tile to calculate the number of days between the two dates.

Alternatively you can copy and paste this code into your Magic ETL 2.0 canvas and it should put the two tiles in automatically for you with everything populated.

{"contentType":"domo/dataflow-actions","data":[{"name":"Rank & Window","id":"9123cd28-6687-43eb-bf78-f0b16293897e","type":"WindowAction","gui":{"x":168,"y":180},"dependsOn":["b3589ae1-28a5-4435-a6d0-fd85afdcdc22"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"Prior Date","operation":{"type":"OFFSET","operationType":"LAG","column":"snapshot_date","amount":"1"}}],"orderRules":[{"column":"snapshot_date","caseSensitive":false,"ascending":true}],"groupRules":[{"column":"sno","caseSensitive":false},{"column":"mmodel","caseSensitive":false}]},{"name":"Date Operations","id":"67aeca27-eade-480c-bac3-a117fa03ffbf","type":"DateCalculator","gui":{"x":300,"y":180},"dependsOn":["9123cd28-6687-43eb-bf78-f0b16293897e"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Days Delay","calcType":"DATE_DIFF","fieldA":"snapshot_date","fieldB":"Prior Date"}]}]}

Hi @Kervin

Your code should work to pull the value from the COLUMN column. If you're needing to select from a different column name based on certain criteria instead of COLUMN then you could utilize a CASE statement inside your CONCAT:

=CONCATENATE("https://b2b.officedepot.com/rest/ODServices/api/product/priceAvailability/xml?zipcode=",
`CONSTANT1`,
"&sku=",
CASE WHEN THIS CONDITION THEN `COLUMN`
WHEN OTHER CONDITION THEN `COLUMN2`
ELSE `ELSE COLUMN` 
END,
"&billto=",
`CONSTANT2` )

Thank you!! I didn't realize that's what the join data tile did... I was using the combine rows and that clearly was the wrong move. I've been able to do exactly what I've been wanting to do, so thank you very much!

You can combine these two datasets very easily in Magic ETL using the Join tile. In Magic ETL, add both datasets (your royalty dataset and your metadata dataset) as input datasets. Then add the join tile and join them on track title. You can then use the select columns tile to select the columns from the metadata dataset and have all the information you need. If you are unfamiliar with the join tile, you can read more about here:

https://domohelp.domo.com/hc/en-us/articles/360042923674-Understanding-Joins

https://domohelp.domo.com/hc/en-us/articles/360044876194-New-Magic-ETL-Tiles-Combine-Data#3.

Hope this helps.

+1 on Grant's solution using Lag(1) .. Lag(2), Lag(3) IF YOU DON"T HAVE GAPS IN YOUR DATA.

Only thing I strongly dislike about this approach is that you have to create 3 functions (not efficient) AND it must be true that there are no gaps in data.

Computationally, @GrantSmith i would say this is probably the only good use case for MySQL b/c it allows you to calculate LAG without the assumption of contigusous data (cc. @MarkSnodgrass

NOTE THIS IS ALL UNTESTED CODE there are probably missing commas, and when you set variables (as opposed to display you must use := and variable names are case sensitive.

if you write

SELECT 
t.*
, r.*
FROM 
table t 
, (SELECT @person := '' , @date := null FROM ) r
ORDER BY t.Date

You are CROSS APPLYing a generated table with one row (r) to every row of table t where table r contains variables person and date which are insantiated as blank and null respectively

from there if you write

SELECT 
@date as prevDate,  
t.*,
@date := t.date as _setDateVariable

FROM 
table t 
, (SELECT @person := '' , @date := null FROM ) r

ORDER BY t.Date

when MySQL reads this function it will process each column of my SELECT statement in order (top to bottom). so when if i was on row 3 of my table...

prevDate is the value of assigned to @date (which happens to be the value of t.date from row 2 -- i'll explain in a second), then it reads all the values for that row of table t, then it assigns @date the value of t.date for the current row (row 3)

when i SELECT row 4...

prevDate is the value of @date (row 3) , t.* is all the contents of row 4, then i assign ( using := ) the value of t.date (row 4) to @date.

and so on.

implement partitioning and test for previous day

SELECT 
-- execute tests. testing @person implements my Partition clause
-- testing @lag1Date ensures the no gaps rule (this is the key part that Magic doesn't give you)

case when t.person = @person and @lag1Date = date_add(t.date, interval -1 day) then @lag1Value end as prevDayValue,
t.*,

-- paritioning assumes an ORDER BY clause
-- OR reset my variables to null
@lag1Value := case when t.person = @person then t.value else null end as _setLag1Value,
@lag1Date := case when t.person = @person then t.date else null end as _setLag1Date,
   
@person := t.person as _setPersionVariable

FROM 
table t 
, (SELECT @person := '' , @date := null , @lag1Value:=null, @lag1Date:=null, FROM ) r

ORDER BY t.person, t.Date

Implement Cascading testing

SELECT 
-- execute tests. testing @person implements my Partition clause
-- testing @lag1Date ensures the no gaps rule (this is the key part that Magic doesn't give you)

case when t.person = @person and @lag1Date = date_add(t.date, interval -1 day) then @lag1Value end as prevDayValue,
case when t.person = @person and @lag2Date = date_add(t.date, interval -2 day) then @lag2Value end as prevDayValue,

t.*,

-- cascade the values of lag1 to lag2 or reset to null
@lag2Value := case when t.person = @person then @lag1Value else null end as _setLag2Value,
@lag2Date := case when t.person = @person then @lag2Value else null end as _setLag2Date,

@lag1Value := case when t.person = @person then t.value else null end as _setLag1Value,
@lag1Date := case when t.person = @person then t.date else null end as _setLag1Date,
   
@person := t.person as _setPersionVariable

FROM 
table t 
, (SELECT @person := '' , @date := null , @lag1Value:=null, @lag1Date:=null, @lag2Value := null lag2Date:=null FROM ) r

ORDER BY t.person, t.Date

Thinking it through another set based way of approaching this without variables would be to do what SQL has to do under the covers (generate windows) for each row in your dataset we'll generate the 4 rows, then we'll assign the value of the transaction for those lagged rows if they exist, then we'll aggregate.

this assumes we have a numbers table, sys_num, that contains integers between 1 and 3 in a column called n.

NOTE this is untested code but i think it works.

P.S. this code you could implement in Magic if you wanted to avoid writing SQL. it would perform faster since we didn't index MySQL.

duplicate (using CROSS APPLY) the rows in t.table 4 times

CREATE TABLE referenceTable as 

SELECT distinct
date_add(t.date, interval n day) as report_date
t.person
t.value as lag_value
n.num as lag_num
t.date as actual_transaction_date
FROM
table t
, (SELECT num from sys_num where num beteen 0 and 3) n

create your windowed data

for each row in table t, you should have duplicate rows if table has a matching 0, 1, 2, or 3 day offset (report_date).

CREATE TABLE blowOut as 

SELECT
t.person,
, t.date,
, r.lag_num
, r.lag_value
,  case when lag_num = 1 then r.lag_value as lag1day end
, case when lag_num =2 then r.lag_value as lag2day end
, case when lag_num=3 then r.lag_value as lag3day end
, case when lag_num=0 then r.lag_value as lag0day end
FROM
table t
JOIN
referenceTable r

on t.person = r.person
and t.date = r.report_date

deduplicate rows

create table lag_table as

SELECT 
max(lag_1day) as lag1day,
max(lag_2day) as lag2day,
max(lag_3day) as lag3day,
max(lag4_day as lag4day,

date,
person

from blowOut

GROUP BY
date,
person

then join in the data for that person and that row

select
t.* 
<lt.columns>

from table t
inner join
lag_table lt
on t.person = lt.person
and t.date = lt.date