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

ozarkram
ozarkram Member
edited July 2022 in Magic ETL

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

image.png


Output table


image.png


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


Tagged:

Comments

  • mhouston
    mhouston Contributor

    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
    GrantSmith Coach

    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
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ozarkram
    ozarkram Member

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

  • ozarkram
    ozarkram Member

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

Categories