Year End

[MickT]

Hi Guys,

I was wondering if it was possible in Qlik script to create multiple year intervals with year end dates from a single start date? 

For example, We have an agreement start date, and want to project a set of years from that date, 

Start date 01/07/17  - 1st End Date 01/07/18 - 2nd End Date 01/07/19 etc etc...for five years..

Hope you can help 

Mick.

[MarcoWedel]

You could just use the AddYears() function here:

table1:
LOAD *,
     AddYears(StartDate,IterNo()) as EndDate
Inline [
StartDate
01/07/17
02/08/18
03/09/19
]
While IterNo() <= 5;

hope this helps

Marco

[MickT]

Hi Marco, 

Thank you for your help, what you have given me is exactly what i need, however I cant seem to fit that into my script. 

I'm trying to adapt your inline data to my tables but not sure where to put the while statement? 

Sorry but havent used iterno() before.. 

Example of data - LLINNO being my id, LLFVDT being the start date, LLAGNB being an agreement no.. 

table1:
LOAD LLINNO,
AddYears(LLFVDT,IterNo()) as EndDate,
LLAGNB

FROM
LTAGLI.qvd (qvd)
WHERE LLPOSX=0
While IterNo() <= 5; ?? 

Thanks 

[MarcoWedel · Accepted solution]

You can't use Where and While together like this, but you can move either of them to a subsequent resident load or a preceding load:

LOAD *,
     AddYears(LLFVDT,IterNo()) as EndDate
While IterNo() <= 5;
LOAD LLINNO,
     LLFVDT,
     LLAGNB
FROM LTAGLI.qvd (qvd)
WHERE LLPOSX=0;

 hope this helps

Marco

[Davis69]

I'm trying to adapt your inline data to my tables but not sure where to put the while statement?  myhtspace

[MickT]

Marco, You Killed it!!  Applied to the production app and all worked brilliantly.. 

Thank you for your time and help. The preceding load was the key I was missing. 

Hope this can help others in the future. 

Appreciate the learning.. 

Cheers 
Mick.