Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I got issue to check the number of consecutive dates of each Vendor Code and its Items.
In my dataset, Vendor Code , Item and Date are dimensions.
Vendor ID | Item | Date |
ABC | 1 | Sept 1 |
ABC | 1 | Sept 8 |
ABC | 1 | Sept 15 |
ABC | 1 | Sept 22 |
DEF | 1 | Aug 4 |
DEF | 1 | Aug 11 |
DEF | 1 | Aug 18 |
DEF | 1 | Aug 25 |
DEF | 1 | Sept 1 |
DEF | 2 | Sept 15 |
XYZ | 2 | May 5 |
XYZ | 2 | June 16 |
XYZ | 2 | July 16 |
Expected Result:
Calculate the Days Aged Field values , Check if its Date field is consecutive week dates within Vendor ID and Item.
I am trying to create a field "Days Aged" in data load editor OR expression ,which is based on Date field , if dates are consecutive week then "Days Aged " starts with 0 and then + 7 within Vendor ID and Item.
the result should be like:
Vendor ID | Item | Date | Expected Field(Days Aged ) | Explanation of Days Aged Values |
ABC | 1 | Sept 1 | 0 | this Vendor Code and Item has consecutive weeks , Sept 1 has no previous week data 0 then +7 days to below dates. |
ABC | 1 | Sept 8 | 7 | |
ABC | 1 | Sept 15 | 14 | |
ABC | 1 | Sept 22 | 21 | |
DEF | 1 | Aug 4 | 0 | this Vendor Code and Item has consecutive weeks , Aug 4 has no previous week data 0 then +7 days to below dates. |
DEF | 1 | Aug 11 | 7 | |
DEF | 1 | Aug 18 | 14 | |
DEF | 1 | Aug 25 | 21 | |
DEF | 1 | Sept 1 | 28 | |
DEF | 2 | Sept 15 | 0 | this Vendor Code and Item Sept 15 has no previous data and no consecutive weeks data. |
XYZ | 2 | May 5 | 0 | this Vendor Code and Item has no consecutive weeks data, May 5 , June 16, and July 16 are not consecutive weeks so Days aged = 0 |
XYZ | 2 | June 16 | 0 | |
XYZ | 2 | July 16 | 0 |
I hope i have made the requirement clear enough.
Any suggestion or tips will be appreciated as i need to get this done quickly.
Thanks!
You could add an additional load in your load script that creates this field with some if statements. Here is an example based on your data.
You could add an additional load in your load script that creates this field with some if statements. Here is an example based on your data.
In my data set I have 3 fields Vendor ID , Item and Date (check my first table). my Expected result will be the 4th calculated field "Days Aged" in my table1 and sorry in my actual data set my date format is already like 10/2/2023.
In your Inline table I can see the "Days Aged" field. does your code work with my table 1 dataset?
Thanks.
I added new_date to make the sorting work. If it is already in your data in that format (10/2/2023), it should work and the new_date is unnecessary.
My inline table loaded "Expected Field (Days Aged)" to check against the calculated "DaysAged" in the second table in my script. The DaysAged calculation should work with your table. It technically references itself in the rangesum function, but it doesn't have to exist before this table to work correctly.
Let me know if I can clarify anything.
No worries , it is working! Thanks I really appreciated :).