Qlik - Add month to date

[jmialoundama]

Hello,

I'm new to Qlik loops

I need to create dates until 2024.

I have the following data:

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.

Please help.. 

[Oleg_Troyansky]

Hi there!

I wouldn't recommend a loop - since Qlik script is an interpreted language, it can get quite slow. Instead, it looks like three load statements would do the trick:

Calendar:
Load distinct
      Date
resident
Table1

;

concatenate (Calendar)
load distinct
         AddMonths(Date,periodicity) as Date
resident
Table1

;

concatenate (Calendar)
load distinct
         AddMonths(AddMonths(Date,periodicity), periodicity) as Date
resident
Table1

;

After these three loads, you will get the desired list of dates.

If you'd like to learn more about advanced scripting, data modeling, performance, and more - consider attending the Masters Summit for Qlik - coming to New Orleans on November 14-16

[jmialoundama]

Hi @Oleg_Troyansky ,

 Thanks a lot for your help.

Indeed, I had also thought of this kind of script but in my example, I forgot to specify that I can have dates in 2020 and 2021.

So if I have a date = 01/01/2020 and a periodicity of 12 months, I will have to create a date for 2021 (01/01/2021), 2022(01/01/2022), 2023(01/ 01/2023) and 2024 (01/01/2024)

if I have a date in 2021 with a periodicity of 12 months, such as 01/03/2021, I will have to create a date in 2022(01/03/2022),2023(01/01/2023) and 2024(01/01/2024).

That's why I want to create a loop because I don't see how to get out of it without a loop.

[Oleg_Troyansky]

Hi @jmialoundama ,

In this case, you should use another type of load, using the WHILE clause. You can generate an unknown number of new rows in a single load, based on a condition. Look up the WHILE load and the function IterNo(), and you should be able to formulate a single load statement that will generate all the dates based on your condition.

Cheers,

[jmialoundama]

Thanks you @Oleg_Troyansky

Can you give me an example of a loop that should be done based on the example I gave please.

I haven't really done a loop in Qlik yet, I'm new to that.

[Oleg_Troyansky]

As I suggested, look up the keywords WHILE and IterNo() in Qlik Help. They offer examples there as well.

Here is the page for IterNo(), with an example that is somewhat similar to what you need.

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/CounterFunct...