topic Re: Joining fields to create date in QlikView

Joining fields to create date

danielnevitt — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have two date fields:

Mat_Year = format YYYY

Mat_Month = format MM

Please can someone help me to join these fields together to create a field in the format MMM-YY.

Thanks,

Daniel

sunny_talwar — Wed, 07 Mar 2018 17:16:48 GMT

May be this

Date(Date#(Mat_Year&'-'&Mat_Month, 'YYYY-MM'), 'MMM-YY') as Mat_MonthYear

kfoudhaily — Wed, 07 Mar 2018 17:26:09 GMT

hello,

in the loading script, in the same table where you are loading these two fields you may add the flowing statement:

date#(Mat_Month&'/'&Mat_Year,'MM/YYYY') as Date

fonction date# to force qlik to read the field as a date and not a string;

regards,

danielnevitt — Wed, 07 Mar 2018 17:35:09 GMT

Hi Sunny,

That works great, thanks for your help.

The only issue I have is that the format is Mmm-YY (Jan-18) rather than MMM-YY (JAN-18).

Is it possible for the output to be in format MMM-YY

Regards,

Daniel

sunny_talwar — Wed, 07 Mar 2018 17:37:01 GMT

Sure.... all you need to do is to change your environmental variable from

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET MonthNames='JAN;FEB;MAR;APR;MAY;JUN;JUL;AUG;SEP;OCT;NOV;DEC';