Calculating number of weeks between two YYYY_WW dates, in while statement generating lines

patricesalem — Thu, 07 Oct 2021 23:18:46 GMT

Hello

I'm using a powerful code in my load statement to duplicate lines based on the number of weeks between two weeks number.
Using my script, the following inline table will show

Load * Inline [ Id,To_be_delivered_on_week, Delivered_on_week 123456_10,202049,202052 ];

The result is exactly as expected...

Unfortunatelly the code only works for weeks in the same year.
The script will duplicate too many lines if we cover two years..., ie:

Load * Inline [ Id,To_be_delivered_on_week, Delivered_on_week 123456_10,202052,202102 ];

This portion of the code will duplicate 50 lines from 202052 to 202102 instead of 3 :

Load $(vMinDate)+ IterNo() as DueDate Autogenerate 1 While $(vMinDate) + IterNo() <= $(vMaxDate) ;

I spent hours trying to find a solution with no luck...maybe some of you will have a good idea !
I will also have to handle the year made of 52 or 53 weeks in order to know when to restart the counter in the loop.
I have attached the QVF and here is code created originally by Stephen Stoichev

RawData: Load * Inline [ Id,To_be_delivered_on_week, Delivered_on_week 123456_10,202052,202102 ]; // Get min and max dates from [To_be_delivered_on_week] and [Delivered_on_week] fields TempTable1: Load min([To_be_delivered_on_week]) as MinDate, max([To_be_delivered_on_week]) as MaxDate Resident RawData ; concatenate Load min([Delivered_on_week]) as MinDate, max([Delivered_on_week]) as MaxDate Resident RawData ; // Get the overall min and max dates NoConcatenate TempTable2: Load min(MinDate) as MinDate, max(MaxDate) as MaxDate Resident TempTable1 ; Drop Table TempTable1; let vMinDate = peek('MinDate'); let vMaxDate = peek('MaxDate'); join (RawData) Calendar: Load $(vMinDate)+ IterNo() as DueDate Autogenerate 1 While $(vMinDate) + IterNo() <= $(vMaxDate) ; Drop Table TempTable2; // Load resident modified RawData table and while loading we'll create new field // This field will be used a flag and we'll filter on it at the end // The logic in the field is: // if DueDate >= [To_be_delivered_on_week] and DueDate <= [Delivered_on_week] then set it to 1 else 0 // The final step is to keep only records with TempFlag == 1 NoConcatenate RawData_Final: Load Id, [To_be_delivered_on_week], [Delivered_on_week], Current_week_of_analysis, nb_of_weeks_late Where TempFlag = 1 ; Load Id, [To_be_delivered_on_week], [Delivered_on_week], DueDate as Current_week_of_analysis, DueDate - To_be_delivered_on_week as nb_of_weeks_late, if(DueDate >= [To_be_delivered_on_week] and DueDate <= [Delivered_on_week], 1, 0) as TempFlag Resident RawData ; Drop Table RawData;

Thanks a lot for your reading

pat

Re: Calculating number of weeks between two YYYY_WW dates, in while statement generating lines

patricesalem — Fri, 08 Oct 2021 19:57:03 GMT

answer to my own question after a few hours of work. enjoy it for those looking for a solution

RawData: Load * Inline [ Id,To_be_delivered_on_week, Delivered_on_week 123456_10,25/12/2020,15/01/2022 ]; // Get min and max dates from [To_be_delivered_on_week] and [Delivered_on_week] fields TempTable1: Load min([To_be_delivered_on_week]) as MinDate, max([To_be_delivered_on_week]) as MaxDate Resident RawData ; concatenate Load min([Delivered_on_week]) as MinDate, max([Delivered_on_week]) as MaxDate Resident RawData ; // Get the overall min and max dates NoConcatenate TempTable2: Load min(MinDate) as MinDate, max(MaxDate) as MaxDate Resident TempTable1 ; Drop Table TempTable1; let vMinDate = peek('MinDate'); let vMaxDate = peek('MaxDate'); join (RawData) Calendar: Load $(vMinDate)+ IterNo() as DueDate Autogenerate 1 While $(vMinDate) + IterNo() <= $(vMaxDate) ; Drop Table TempTable2; // Load resident modified RawData table and while loading we'll create new field // This field will be used a flag and we'll filter on it at the end // The logic in the field is: // if DueDate >= [To_be_delivered_on_week] and DueDate <= [Delivered_on_week] then set it to 1 else 0 // The final step is to keep only records with TempFlag == 1 NoConcatenate RawData_Final: Load Id, To_be_delivered_on_week, Delivered_on_week, Current_week_of_analysis ,nb_of_weeks_late Where TempFlag = 1 ; Load min(1) as Dummy, Id, year(To_be_delivered_on_week) & num(week(To_be_delivered_on_week),'00') as To_be_delivered_on_week, year(Delivered_on_week)&num(week(Delivered_on_week),'00') as Delivered_on_week, year(DueDate)&num(week(DueDate),'00') as Current_week_of_analysis, (WeekEnd(DueDate)-WeekEnd(To_be_delivered_on_week))/7 as nb_of_weeks_late, if(year(DueDate)&num(week(DueDate),'00') >= year(To_be_delivered_on_week) & num(week(To_be_delivered_on_week),'00') and year(DueDate)&num(week(DueDate),'00') <= year(Delivered_on_week)&num(week(Delivered_on_week),'00'), 1, 0) as TempFlag Resident RawData group by Id, year(To_be_delivered_on_week) & num(week(To_be_delivered_on_week),'00'), year(Delivered_on_week)&num(week(Delivered_on_week),'00'), year(DueDate)&num(week(DueDate),'00'), (WeekEnd(DueDate)-WeekEnd(To_be_delivered_on_week))/7, if(year(DueDate)&num(week(DueDate),'00') >= year(To_be_delivered_on_week) & num(week(To_be_delivered_on_week),'00') and year(DueDate)&num(week(DueDate),'00') <= year(Delivered_on_week)&num(week(Delivered_on_week),'00'), 1, 0) ; Drop Table RawData;

topic Calculating number of weeks between two YYYY_WW dates, in while statement generating lines in App Development

Calculating number of weeks between two YYYY_WW dates, in while statement generating lines

Re: Calculating number of weeks between two YYYY_WW dates, in while statement generating lines