Loops through months in Qlik Sense

Question

Could anyone help me to get a solution for the below scenario? (Date Format is 'MM/DD/YYYY')

Cr Date is an account created date, End Date is an account end date, # of Months means the months between created date and end date, Due Date means the day number of each month the payment gets due.

I need to generate this Due Date field as a date like below which is continuos due dates between created date and end date.

1/8/2020

2/8/2020

3/8/2020

4/8/2020

5/8/2020

6/8/2020

Could you please help me out to find a solution for this task?

Answer 1

One possible solution is to loop through all value and autogenerate the dates.

Example annotated load script:

// Sample data
RawData:
Load * Inline [
  Id, Cr Date   , End Date
  1 , 12/08/2019, 06/08/2020
  2 , 05/07/2019, 16/11/2020
];


// temp table to keep distinct values 
// of the concatenation of id, cr date and end date
// example record:
// 1^12/08/2019^06/08/2020
TempTable:
Load
  distinct
  Id & '^' & [Cr Date] & '^' & [End Date] as Id_Dates
Resident
  RawData
;

// for each record in Id_Dates field
for i = 1 to FieldValueCount('Id_Dates')
  // get the current iteration value
  let value = FieldValue('Id_Dates', $(i));
  // extract the Id
  let currentId = SubField('$(value)', '^', 1);
  // extract cr date  
  let currentCrDate = Num(SubField('$(value)', '^', 2));
  // extract end date
  let currentEndDate = Num(SubField('$(value)', '^', 3));

  // autogenerate all dates between the currentCrDate and currentEndDate
  // add the current Id value (this will link to the RawData table
  DueDates:
  LOAD
    '$(currentId)' as Id,
    date($(currentCrDate) + IterNo() - 1, 'DD/MM/YYYY') AS DueDate
  AUTOGENERATE (1)
  WHILE 
    $(currentCrDate) + IterNo() -1 <= $(currentEndDate)
  ;

next

// we dont need this table anymore
Drop Table TempTable;

Once the script has finished it will contain two tables:

And DueDates table will contain values like this:

P.S. This solution might not be very efficient if the source data have a lot of distinct Id values. Let me know if this is the case and I'll think of some other solution

Update (06/10/2021)

Another approach that avoids looping through all the rows individually is to create cross join (cartesian join) between the source data and calendar table that have all possible dates. Once we have this table we can then filter out the rows that are not required

This approach will be faster but it will most likely consume more RAM durin the reload. Once the reload is complete the result app should have the same memory footprint as the "loop through each row" approach

RawData:
Load * Inline [
  Id, Cr Date   , End Date
  1 , 12/08/2019, 06/08/2020
  2 , 05/07/2019, 16/11/2020
];

// Get min and max dates from [Cr Date] and [End Date] fields
TempTable1:
Load 
    min([Cr Date]) as MinDate,
    max([Cr Date]) as MaxDate
Resident
    RawData
;

concatenate

Load 
    min([End Date]) as MinDate,
    max([End Date]) 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;

// gnerate all possible dates between the min and max dates
// once the dates are generated join the result table to RawData
// since there is no common fields between both tables 
// the result table will be many to many join (cartesian join)
// as a result at this point RawData will be quite large table
// No of rows in RawData (initially) * No of rows in the Calendar table
// for example if RawData has 10 rows and calendar have 1000 the result table
// will have 10 000 rows
// We will reduce the rows a bit in the next step
let vMinDate = peek('MinDate');
let vMaxDate = peek('MaxDate');

join (RawData)

Calendar:
Load
  Date($(vMinDate) + IterNo() - 1, 'DD/MM/YYYY') as DueDate
Autogenerate 1
While 
    $(vMinDate) + IterNo() - 1 <= $(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 [Cr Date] >= DueDate <= [End Date] then set it to 1 else 0
// The final step is to keep only records with TempFlag == 1
NoConcatenate

RawData_Final:
Load 
  Id,
  [Cr Date],
  [End Date],
  DueDate
Where 
  TempFlag = 1  
;
Load 
  Id,
  [Cr Date],
  [End Date],
  DueDate,
  if(DueDate >= [Cr Date] and DueDate <= [End Date], 1, 0) as TempFlag
Resident
    RawData
;

Drop Table RawData;

Answer 2

@Stefan Stoichev

Thanks for your excellent code...nevertheless my ID values table contains around 200000 entries...it makes the load script quite slow. Have you thought about another method ?

Thanks Here is the adaptation of your code I'm using

[ZSQ042_TEMP]:
LOAD
    key //commande & poste
    ,[COMMANDE]
    ,[POSTE]
    , YEAR_WEEK_REF_TAUX_SERVICE
    , WEEK_SM_REELLE_ZSQ
    
    ,[ORIGINE]
    ,[Business unit]
    ,[Sector]
    ,TYPE_cde
    
 resident [SALES]
 where num(WEEK_SM_REELLE_ZSQ) > num(YEAR_WEEK_REF_TAUX_SERVICE)
 ;
 
 
//on duplique toutes les lignes qui ne sont pas livrées à la semaine annoncée
 
 TempTable:
Load
  distinct
  key & '^' & COMMANDE & '^' & POSTE & '^' & YEAR_WEEK_REF_TAUX_SERVICE & '^' & WEEK_SM_REELLE_ZSQ & '^' &
  [ORIGINE] & '^' & [Business unit] & '^' & [Sector] & '^' & TYPE_cde
  as Id_Weeks
Resident
  [ZSQ042_TEMP]
;


// for each record in Id_Dates field
for i = 2 to FieldValueCount('Id_Weeks')
 
 // get the current iteration value
  let value = FieldValue('Id_Weeks', $(i));
  
  // extract the Id
  let currentId = SubField('$(value)', '^', 1);
  
  //Extract Order Number
  let currentOrder = SubField('$(value)', '^', 2);
  
  //Extract Order Line Number
  let currentLine = SubField('$(value)', '^', 3);
    
  // extract week to be delivered  
  let currentWeekStart = Num(SubField('$(value)', '^', 4));
  
  // extract week of delivery
  let currentWeekEnd = Num(SubField('$(value)', '^', 5));
  
  // extract origin
  let currentOrigin = SubField('$(value)', '^', 6);
  
  // extract BU
  let currentBU = SubField('$(value)', '^', 7);

// extract Sector
  let currentSector = SubField('$(value)', '^', 8);
  
  // extract Type de commande
  let currentType = SubField('$(value)', '^', 9);
   
// autogenerate all weeks between the week to deliver and week of delivery
   // add the current Id value (this will link to the RawData table)


Concatenate([SALES])
LOAD 
    '$(currentId)' as key,
    '$(currentOrder)' as COMMANDE,
    '$(currentLine)' as POSTE,
    '$(currentWeekStart)' as INITIAL_YEAR_WEEK_REF_TAUX_SERVICE,
    $(currentWeekStart) + IterNo() as YEAR_WEEK_REF_TAUX_SERVICE, //IterNo()-1
    '$(currentWeekEnd)' as WEEK_SM_REELLE_ZSQ,
    IterNo() as Nb_Weeks_Late,
   
    
    
    '$(currentOrigin)' as ORIGINE,
    '$(currentBU)' as Business_unit,
    '$(currentSector)' as Sector,
    '$(currentType)' as TYPE_cde
    
    
    
  AUTOGENERATE (1)
  WHILE 
    $(currentWeekStart) + IterNo() <= $(currentWeekEnd) //IterNo()-1
  ;

next

// we dont need this table anymore
Drop Table TempTable;
Drop Table ZSQ042_TEMP;