Create a date variable using variables within my original data

[#1]

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

[swuehl]

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

[#3]

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."

[swuehl]

Mike, can you post your code snippet here?

[#5]

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 ......

[swuehl]

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 ...

[#7]

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