How to check Consecutive dates ?

[Sara_3]

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!

[Sara_3]

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!

[KGalloway · Accepted solution]

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.

original_table:

load 

*

inline [

Vendor ID, Item, Date , new_date, Expected Field(Days Aged )            

ABC, 1, Sept 1, 9/1/2023, 0

ABC, 1, Sept 8, 9/8/2023, 7  

ABC, 1, Sept 15, 9/15/2023, 14  

ABC, 1, Sept 22, 9/22/2023, 21  

DEF, 1, Aug 4, 8/4/2023, 0

DEF, 1, Aug 11, 8/11/2023, 7  

DEF, 1, Aug 18, 8/18/2023, 14  

DEF, 1, Aug 25, 8/25/2023, 21  

DEF, 1, Sept 1, 9/1/2023, 28  

DEF, 2, Sept 15, 9/15/2023, 0

XYZ, 2, May 5, 5/5/2023, 0

XYZ, 2, June 16, 6/16/2023, 0  

XYZ, 2, July 16, 7/16/2023, 0

]

;

noconcatenate

final_table:

Load

*,

    if([Vendor ID] = previous([Vendor ID]) and Item = previous(Item) and date(new_date - 7) = previous(new_date), 

    RangeSum(peek('DaysAged'),7), 0) AS DaysAged

resident original_table

order by [Vendor ID], Item, Date;

drop table original_table;

[Sara_3]

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.

[KGalloway]

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.

[Sara_3]

No worries , it is working! Thanks I really appreciated :).