Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Create a date variable using variables within my original data

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

6 Replies
MVP
MVP

Create a date variable using variables within my original data

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

Not applicable

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

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

MVP
MVP

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

Mike, can you post your code snippet here?

Not applicable

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

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

MVP
MVP

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

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

Not applicable

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

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

Community Browser