topic Re: Master Calendar in QlikView

Master Calendar

vireshkolagimat — Sun, 20 Aug 2017 14:15:35 GMT

Hi All,

I got the below script while creating the master calendar. Could somebody please explain it.

On what basis we taking the multiplier. Year is multiplied by 1000 and month by 100 & day nothing.

Year(Temp_Date) * 10000 + Month(Temp_Date) * 100 + Day(Temp_Date) as [DateKey],

Regards,

Viresh

Re: Master Calendar

Frank_Hartmann — Sun, 20 Aug 2017 15:46:25 GMT

actually you are just determining the digits of your datefield by multiplying.

e.g.

Year(Now()) * 10000 = 2017 * 1000 = 20170000

Month(Now()) * 100 = 08 * 100 = 0800

Day(Now()) = 20

20170000 + 0800 + 20 = 20170820

Re: Master Calendar

swuehl — Sun, 20 Aug 2017 15:46:46 GMT

Day shows at max 2 digits, month shows at max 2 digits.

So by multiplying Month with 100 and Year with 100*100, you ensure that Year, Month and Day values keep seperated when adding up the values, to ensure that each DateKey value represents an unambiguous Date value.

Re: Master Calendar

effinty2112 — Sun, 20 Aug 2017 15:52:33 GMT

Hi Viresh,

This script will create dates in the format YYYYMMDD.

20th Aug 2017

Year(20th Aug 2017)*10000 = 20170000

Month(20th Aug 2017)*100 = 800

Day(20th Aug 2017) = 20

20170820

Cheers

Andrew

Re: Master Calendar

swuehl — Sun, 20 Aug 2017 16:17:49 GMT

You should be able to alternatively use

Num#(Date([Temp_Date],'YYYYMMDD')) as [DateKey]

You need the Num#() to not only format the text representation of the temp date as desired, but to create a numeric value that matches the format.

QlikView Date fields

Re: Master Calendar

vireshkolagimat — Mon, 21 Aug 2017 08:20:24 GMT

Hi Stefan,

thanks for the clarification.

Regards,

Viresh