Unique ID within parameter

user027926

I'm working on an ETL. I want to create a unique ID for each row of data with the caveat that the ID should be the same as the previous value if a value 0 appears in a flagged column. I was thinking of doing this through a window function of row number and then a function tile, but I can't figure out how to achieve this (and then want to sum the order values within the flagged areas (this is within the confines of the same account only). Here's an example:

Account # Close Date Flag Unique ID Order Value Total Order Value

ABCD 01/22/2022 1 1 $50 $50

ABCD 02/23/2022 1 2 $100 $310

ABCD 04/21/2022 0 2 $200 Null/N/A

ABCD 05/11/2022 0 2 $10 Null/N/A

EFGH 01/22/2022 1 3 $50 $50

IJKL 02/25/2022 1 4 $10 $30

IJKL 04/21/2022 0 4 $20 Null/N/A

IJKL 05/11/2022 1 5 $70 $70

Any ideas? Thanks!

MarkSnodgrass

You could utilize the Lag function in the rank & window tile get the previous row's flag next to the current row's flag as another column. Then use the formula tile to overwrite the unique id if the previous and current flag are the same. Finally, you could use the rank & window again and use the row number function and partition on the account number field.

Hopefully I understood your request correctly and that this helps.

user027926

Thanks @MarkSnodgrass -- this is the right logic (only I'd change the flags being equal to them summing to less than or equal to 1, to include the first value as well). Where I keep getting is stuck is how to do a previous function (or lag) within the function tile. It doesn't seem like its supported. (That would be for this step of your recommendation: 'Then use the formula tile to overwrite the unique id if the previous and current flag are the same.'

Thanks!

MarkSnodgrass

@user027926 you can't use a lag function in a formula tile within Magic ETL. That is why I suggested doing a lag first with the rank & window tile to put the previous value next to your current value as a new column. Once you have the previous value in the same row as your current value, you could do the formula work that you want to do.

I'm not sure if it will help (and I tend to stay away from using them regularly) but you can do window functions at the card level. It is not enabled by default, so you need to as your CSM to "enable window functions in beast modes." You are wanting to do quite a few things, so I don't know that you can do it all at the card level, but thought I would mention it in case it is helpful to you.

user027926

Thanks @MarkSnodgrass . The card level won't work for this, but thanks for the mention.

The issue that I'm having (if I understand your suggestion correctly) is that the lag function is consistent to one line. I need the lag function to be conditional, as something it needs to go back a few rows (until it hits a row with a flag of 1). I've reincluded the data sample here but put it in a chart in hopes that the formatting retains so that the issue is clear (the order value column are less essential, as that's just a group by, but I included it, in case there's an easier way). Thanks again for all your help!