CODEWITHSUNDEEP
loopsdateqliksense
Juji
Juji

Reputation: 21

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

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

Upvotes: 0

Views: 194

Answers (1)

Stefan Stoychev
Stefan Stoychev

Reputation: 4987

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

Upvotes: 0

Related Questions