topic Re: How to combine date fields in QlikView

How to combine date fields

Thu, 12 Mar 2015 00:50:04 GMT

Hello,

I need to combine some date fields into one date. Currently the data looks like this.

Month in MM format

Year in YYYY format

I would like to combine them together into a new field and have it displayed in MM/YYYY format.

Any help would be much appreciated.

Thanks - Jason

Re: How to combine date fields

r_wroblewski — Thu, 12 Mar 2015 01:23:23 GMT

Hi Jason

you can simply use

[Month] & '/' & [Year] as [YearMonth],

in your script. Problem is that this new field is a string.

Better solution would be

Date(MakeDate([Year],[Month]),'MM/YYYY') as [YearMonth],

in your script.

Let me know if it works for you.

Best regards,

Ronny

Re: How to combine date fields

Thu, 12 Mar 2015 01:42:08 GMT

So that worked perfect. My issue now is that the Months are coming in as names and I converted them into numbers in the table but I cannot seem to use the new field (InfoMonthNumber) unless I do a resident load on a new tab or below the load. Is there anyway around this? I would like the YearMonth to be in the same table.

InfoSec:

LOAD Site as OU,

[Lumension Installs],

[Systems in AD],

[Install Percent],

[Microsoft Patch %],

[3rd Party Patch %],

Month as InfoSec_Month,

Year as InfoSec_Year,

num(month(date#(Month,'MMM'))) as InfoMonthNumber //Convert Month name into Month Number

FROM

[..\Patch.xlsx]

(ooxml, embedded labels, table is Sheet1);

DateConversion:

Load

InfoSec_Year,

InfoMonthNumber,

Date(MakeDate([InfoSec_Year],[InfoMonthNumber]),'MM/YYYY') as [InfoSecMonthYear]

Resident InfoSec

;

Re: How to combine date fields

r_wroblewski — Thu, 12 Mar 2015 02:06:58 GMT

Hi Jason

just use left join function.

Left Join(InfoSec)

Load *

Resident DateConversion;

Regards,

Ronny

Re: How to combine date fields

Thu, 12 Mar 2015 02:19:50 GMT

That worked, thanks for the help.