Skip to content

    • Magic ETL

    Magic ETL

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

    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..

    Excel
    Sample data for new or old order system.xlsx
    Uploaded 10.86 KB


    Tagged:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Comments

    • 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.

    • Coach

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

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

      A week prior:

      1. `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:

      1. 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!**
    • Member

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

    • Member

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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories