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

[patricesalem]

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

[patricesalem · Accepted solution]

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;