topic Re: Calendar script in QlikView

Calendar script

Thu, 29 Nov 2012 10:40:12 GMT

[Calendar]:

LOAD *

,dual('Q' & "Quarter Only" & ' ' & "Year Only",quarterstart("Date")) as "Quarter"

;

LOAD *

,"Month Only"/3 as "Quarter Only_testing","Month Only" as "Quarter Only_testing1",

ceil("Month Only"/3) as "Quarter Only_test",

'Q'&ceil("Month Only"/3) as "Quarter Only"

;

LOAD *

,date(monthstart("Date"),'MMM YYYY') as "Month"

,date(yearstart("Date"),'YYYY') as "Year"

,month("Date") as "Month Only"

,year("Date") as "Year Only"

;

LOAD date(today() + 10000 - recno()) as Date

AUTOGENERATE 2000

;

Can you pls any one explain me about this script.

Thanks,

Re: Calendar script

er_mohit — Thu, 29 Nov 2012 11:13:54 GMT

u will read about this either through in refrence manual or in qlikview help.. and it must be clear from that.

hope this help....

Re: Calendar script

Peter_Cammaert — Thu, 29 Nov 2012 11:30:46 GMT

It's usually better to start a discussion with a specific question, instead of "please explain QlikView scripting".

In short:

3 PRECEDING LOADs and one LOAD AUTOGENERATE to create single table CALENDAR

Each PRECEDING LOAD takes its data from the next line (by way of RESIDENT tables)

Read the script in reverse:

LOAD 4: Generate a fixed set of 2000 dates somewhere in the future
LOAD 3: Calculate and add MonthStart, YearStart, Month and Year values to each date field
LOAD 2: Add various intermediate values while calculating a "Quarter" value for each date
LOAD 1: Add a dual (e.g. text and number) Quarter field to each date

That's about it.

Peter

Re: Calendar script

Thu, 29 Nov 2012 11:35:11 GMT

I understand, but I didn't get this line

LOAD date(today() + 10000 - recno()) as Date

AUTOGENERATE 2000;

Can you pls explain.

Thanks,

Re: Calendar script

Peter_Cammaert — Thu, 29 Nov 2012 11:53:23 GMT

That line generates 2000 records numbered from 1 to 2000. RecNo() returns the number of the record that is currently being generated. This value is subtracted from today()+10000 e.g. 10000 days in the future.

So the first record has a date of today() + 9999 days, the second today() + 9998 days etc.

Peter

Re: Calendar script

Thu, 29 Nov 2012 11:53:41 GMT

I agree Peter, will do next time onwards.

Thanks,