topic Year End in QlikView

Year End

MickT — Tue, 22 Feb 2022 22:21:14 GMT

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 — Wed, 23 Feb 2022 21:11:12 GMT

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 — Thu, 24 Feb 2022 00:27:59 GMT

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 — Thu, 24 Feb 2022 19:48:52 GMT

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 — Tue, 01 Mar 2022 04:31:27 GMT

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

MickT — Fri, 25 Feb 2022 10:31:01 GMT

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.