Combine Year and Month in 1 Date column

[brunopaulo]

Hello Community,

I'm trying to resolve a date problem. I have the year column and the month column. I want to put it all together into a single column, which is a date type. I'dont want ID type (20141) nor do I want it to be a string. I want it to be recognizable as a date. Can anyone help?

Best Regards

Bruno Paulo

[Colin-Albert]

Use the makedate() function

makedate(year, month) will give you a date of the 1st of the month.

[sunny_talwar]

or may be this if Month looks like this Jan, Feb, Mar... etc

Date(MakeDate(Year, Month(Date#(Month, 'MMM')))) as Date

Alternatively, you can use Date#() function

Date(Date#(Month & '-' & Year, 'MMM-YYYY')) as Date

Also, look here:

Why don’t my dates work?

QlikView Date fields‌

Get the Dates Right

[brunopaulo]

First of all thank you for the help. I already saw something about the MakeDate function, where should i place it. On the load statment or creat a table resident on the table where i want combine ?

[Main Data]:

LOAD Year,

     Month,

     Value

FROM

qvd file

and i want

[Main Data]:
Date
Value

FROM

qvd file

[sunny_talwar]

Like this

[Main Data]:

LOAD Year,

    Month,

    Date(MakeDate(Year, Month(Date#(Month, 'MMM')))) as Date1,

    Date(Date#(Month & '-' & Year, 'MMM-YYYY')) as Date2,

    Value

FROM

qvd file

[brunopaulo]

Thank you for the help. Solved my problem.

Best Regards

Bruno Paulo

[sunny_talwar]

Super

[brunopaulo]

And now in this case:

[Main Data]:

LOAD Year,

     Month as NumericMonth,  // it's only numbers jan is 1, fev is 2,

     Month(MakeDate(2000,Month)) as Month,

       Values

From qvd file

Drop field NumericMonth

[sunny_talwar]

Try to convert 1, 2, 3 into Jan, Feb, Mar? Try this

Month(Date#(Month, 'M')) as Month

[brunopaulo]

Place after the Load of month?

I did