Lag a pre-existing column?

myouness
myouness Member
edited March 2024 in Magic ETL

Hi there, I'm wondering if it's possible (with Magic ETL or beast mode) to creation functions (like rank and window) that can lag value from Column 1 to Column 2, where both columns already exist.

Full context:

We have 3 columns. Monthly total, Last month, Year to date. I'm trying to generate a new YTD total every month by adding the monthly total to last month's YTD. My current ETL process is recursive but I can't verify if it's working, and I'd like to remove any problem points.

ETL flow should look like [data/monthly total already present] > generate last month's total > add those two values together.

Generating last month's total is using rank and window with lag, currently, but because it's pulling from column 3, which doesn't have a value until after the rank and window step, so the reality is that it looks like:

[data/monthly total already present] > create blank YTD > generate last month's total (from YTD) > calculate actual YTD from those values > add the output to the beginning of the ETL flow, joining to the "create blank YTD" and populates the result from the previous run.

It just seems senselessly complicated, and I'm open to any other ideas of how we can do this creatively. It'd be great if the rank and window lag could drop the values of last month into a column that already exists, so I could put the addition function earlier in the ETL and remove the need for a recursive method. Month columns at the beginning are how totals are sorted, just YYYY-MM.

Tagged:

Best Answer

Answers

Categories