0 Replies Latest reply: Jan 17, 2017 10:05 AM by Chris Walsh RSS

    Tracking Changes - Concatenate / Join

    Chris Walsh

      Hi

      I have a table that contains the following Key Fields:

       

      Req Date, Order Number, Line Number, Qty

       

      I am adding a column called "Status" by adding the following:

       

      IF(today()-[Req Date]<=7,'1to7',if(today()-[Req Date]<=14,'8to14',if(today()-[Req Date]<=21,'15to21',if(today()-[Req Date]<=28,'22to28','Over28'))))

       

      and adding this field so I can refer back to what a position was at a selected date:

       

      today() as [Date Added]

       

      So I end up with a table containing the following fields Req Date, Order Number, Line Number, Qty, Status,Date Added

       

      I need to track the following:

       

      New items:

      Items that were not in arrears but are now

       

      Status Change:

      Items that were on the report previously but now moved Status, e.g. they were 1to7 days yesterday, but today they are 8to15days old.

       

      Cleared:

      Items that were in arrears before but are no longer (they will disappear from the main import if they are no longer in arrears)

       

      What I was thinking is create a master table (Called "Master") which would be my starting point. I would then do the following when the import runs each morning:

       

      1) Find what is new and store in the "New Arrears" table:

      So I would check for items that exist in the import but not in the "Master" table and store these in "New Arrears" (along with today() as [Date Added])

       

      2) Find Items where the status has changed and store in the "Changes" table:

      I would check for items that exist in the "Master" table AND in the Import, but where "Status" is different - I would then store these in the "Changes" table (along with today() as [Date Added])

       

      3) Find Items that have been cleared and store in the "Cleared" table:

      I would check for items that exist in the "Master" table but not in the Import - I would then store these in the "Cleared" table (along with today() as [Date Added])

       

      4) Updated the "Master" table ready for the next run:

      I would then add all items that exist in the import but not in the "Master" table into the "Master" table ready for the next time the report is run.

       

      I will then be able to select a date and see what was in arrears, how old it was, what was getting older and what had been cleared in a graph.

       

      I've play around a lot with concatenate but I'm struggling as it's the first time I've really used it in this way.

       

      Please can someone point me in the right direction or advise a better way to do this? An example would be great if someone could take the time to do that.

       

      Thanks in advance

      Chris