Beast Mode to compare the record from the recent Monday to the last Monday

ozarkram · 2022-07-19T20:06:14+00:00

There was an error rendering this rich post.

Hi All,

I have a dataset that has the the different schedule codes by date at a PO number level..I am trying to identify the records from the most recent monday and compare it to the immediate last monday and then flag the combination of (POnumber /schedule_code) to be old (meaning this combination existed the last monday otherwise I have to flag it as new.. I have to consider only the most recent monday and the immediate last monday for consideration

Input table

Output table

In the above case 07/18 is the most recent monday and 07/11 is the immediate last monday for comparison...Also attached the excel sheet data

Can you please help me here..

Sample data for new or old order system.xlsx

Find more posts tagged with

Magic ETL

Comments

mhouston

I would do this in an ETL by filtering your dataset to this monday, and then using a second branch, filtering to last monday. Then you can right join last monday to this monday on date, po number, and schedule code. You can use a formula tile and set it to "new" when the left side po number is empty, and "old" when the left side po number is populated.

GrantSmith

You can utilize a formula to calculate the most recent Monday for the week:

`Date` - INTERVAL (CASE WHEN DAYOFWEEK(`Date`) = 1 THEN 6 ELSE (DAYOFWEEK(`Date`) - 2) END) DAY

A week prior:

`Date` - INTERVAL (CASE WHEN DAYOFWEEK(`Date`) = 1 THEN 6 ELSE (DAYOFWEEK(`Date`) - 2) END) DAY - INTERVAL 7 DAY

DAYOFWEEK returns a number between 1 (Sunday) and 7 (Saturday).

The CASE statement is checking if it's a Sunday to subtract 6 days instead of 1.

-2 is to reduce the days to subtract to get to Monday. For example Tuesday (3) we need to subtract only 1 day instead of 3 days.

You can then filter your data where the date and the Monday date match. Twice - once for this week, once for last week.

To populate the New vs Old use a Formula tile as @mhouston recommended:

CASE WHEN `Left PO Column` IS NULL THEN 'New' ELSE 'Old' END

ozarkram

Thank you so much @GrantSmith !..Really appreciate your help!

ozarkram

Thank you so much @mhouston !..really appreciate your help!