topic Re: Create a date variable using variables within my original data in QlikView

Create a date variable using variables within my original data

Mon, 12 Sep 2011 12:24:58 GMT

Hi,

My data set has variables called month and year, I would like to combine these with the day being equal to 1 in order to make a date. The reason behind this is so that I can then use the time series graphs to be able to properly compare time periods over the last 5 years (as an example).

I was trying to do this within the script editor but I'm not sure of the syntax. I know how to create this in excel (or SAS code), I'm just not quite there yet with Qlikview!

Alternatively, if there is an easier way, I'm open to all advice!!

Thanks in advance!

Rob

Create a date variable using variables within my original data

swuehl — Mon, 12 Sep 2011 23:23:41 GMT

Hi Rob,

I think you can just add a line to your LOAD, like:

LOAD

Month,

Year,

Makedate(Year,Month) as Date,

...

from Table;

MakeDate(Year, Month) creates a date: 1st day of Month in Year (this is equivalent to write Makedate(Year, Month, 1), since Day parameter defaults to 1).

I hope this is what you want, if not, just post your excel (preferred, or SAS) code.

Regards,

Stefan

Re: Create a date variable using variables within my original data

Wed, 29 Aug 2012 16:33:01 GMT

did you get this to work?

I do a makedate on the load script and it says "'Makedate' is not a recognized built-in function name."

Re: Create a date variable using variables within my original data

swuehl — Wed, 29 Aug 2012 16:46:11 GMT

Mike, can you post your code snippet here?

Re: Create a date variable using variables within my original data

Wed, 29 Aug 2012 20:36:08 GMT

swuehl - thanks for the reply....

I acutally figured out the first problem - had the makedate in the SELECT and not the LOAD.....

but still not getting out exactly what I want with the code below - the month and year are coming in as string from the DB so I thought I needed to convert to number for the makedate....

the below code results in a - in the Date field. same result without the Num# conversion

LOAD

......

Num#(Month,'0') as Month,

Num#(Year,'0') as Year,

Makedate((Num#(Month,'0')), (Num#(Year,'0'))) as Date;

SQL SELECT ......

Re: Create a date variable using variables within my original data

swuehl — Wed, 29 Aug 2012 21:27:35 GMT

makedate() function takes year as first argument, then an optional month as second, an optional day as third.

LOAD

...

Makedate( (Num#(Year,'0')),(Num#(Month,'0'))) as Date;

SQL SELECT ...

Re: Create a date variable using variables within my original data

Wed, 29 Aug 2012 21:41:23 GMT

Ah - right - stupid error on my part. Thanks for the help!