Search code examples
loopsdateqliksense

Qlik - Add month to date

I'm new to Qlik loops

I need to create dates until 2024. I have the following data: enter image description here

The Date column is a date that I retrieve from my database The Periodicity column (these are months). This column I retrieve directly from my database.

Column Date 1 and Date 2 are columns I need to calculate in Qlik.

I want to loop to calculate these two columns, and the calculations are as follows: Example for PN 10101, Date: 01/02/2022, Periodicity = 12

  • Date_1 = AddMonths(Date,periodicity) ----> 01/02/2023
  • Date_2 = AddMonths(Date_1,periodicity) -----> 01/02/2024

I only want to have 2 dates by PN (One date in 2023 and another in 2024). I don't want to generate multiple dates until 2024.

thank you in advance for your help

Solution

  • No need to loop in this case.

    DataBaseData:
Load
  PN,
  Date,
  // Define Date_1
  AddMonths(Date,Periodicity) as Date_1,
  // Wrap AddMonths into another AddMonths to get the second field
  AddMonths(Date,AddMonths(Date,Periodicity), Periodicity) as Date_2,
  Periodicity
From
  [some-database]
;

    The other approach is to use Preceding LOAD feature.

    TLDR; Preceding load allows you to have nested loads that results in one table. Each preceding load takes the inner load result as an input.

    DataBaseData:
// In the second step use the already available Date_1
// field and calculate Date_2
// * - will load all fields from the previous table
Load
  *,
  AddMonths(Date_1,Periodicity) as Date_2,
;
// In the first step calculate Date_1 fiels
Load
  PN,
  Date,
  AddMonths(Date,Periodicity) as Date_1,
  Periodicity
From
  [some-database]
;

    P.S. The order of loading/executing, in the case of preceding load, is from bottom to top