topic Re: Date Format in QlikView

Date Format

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

Hi,

I have a field that is named period_cd and is text in the format MMM-YY (JAN-18). Is there a way that I can easily convert this to format YYYYMM (201801)?

Thanks,

Daniel

Re: Date Format

Chanty4u — Wed, 07 Mar 2018 13:22:33 GMT

try

date(Date#(Datefield,'MMM-YY'),'YYYYMM') as NewDate

Re: Date Format

Anonymous — Wed, 07 Mar 2018 13:25:55 GMT

Hi ,

Use the below syntax

Date(Date#(Datefield,'MMM-YY') , 'YYYYMM')

Thanks & regards,

Venkata Sreekanth

Re: Date Format

danielnevitt — Wed, 07 Mar 2018 13:31:38 GMT

Thank you both for the replies.

Unfortunately the above code does not work.

APR-18 returns 201800 (rather than 201804). Is this because the period_cd field is text rather than a date?

Regards,

Daniel

Re: Date Format

Chanty4u — Wed, 07 Mar 2018 13:33:07 GMT

cn you share sample data?

Re: Date Format

YoussefBelloum — Wed, 07 Mar 2018 13:35:51 GMT

what do you have on your Monthnames variables ?

do you have this: Set MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'; ??

Re: Date Format

danielnevitt — Wed, 07 Mar 2018 13:37:33 GMT

Hi,

The variables I have set are:

SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET NullDisplay='-';

Regards,

Daniel

Re: Date Format

YoussefBelloum — Wed, 07 Mar 2018 13:54:49 GMT

"Is this because the period_cd field is text rather than a date?"

Even if this field is a text, using Date#() the text will be evaluated as a Date.

Re: Date Format

danielnevitt — Wed, 07 Mar 2018 14:00:08 GMT

Hi,

The code you posted, but I think got deleted from the above communication works:

=Date(Date#(right(period_cd,2)&num(month(date#(left(period_cd,3),'MMM'))),'YYMM')),'YYYYMM')

Regards,

Daniel

Re: Date Format

danielnevitt — Wed, 07 Mar 2018 14:01:33 GMT

I also have two other fields:

Mat_Year = format yyyy

Mat_Month = format mm

Is it possible to join these fields together to create format mmm-yy?

Regards,

Daniel

Re: Date Format

YoussefBelloum — Wed, 07 Mar 2018 14:02:42 GMT

Yes I deleted it because I thought I could do better.. like sureshqv‌'s expression.

Good to know it is working for you

Re: Date Format

YoussefBelloum — Wed, 07 Mar 2018 14:06:10 GMT

if Mat_year and Mat_month are related to the date Period_cd, I suggest you yo delete them from your table and to create a derived mmm-yy field from your Period_cd field on the master calendar.

Re: Date Format

danielnevitt — Wed, 07 Mar 2018 14:10:37 GMT

They are coming from a different data source. I have four different data sources and one of the common joins is period_cd in format MMM-YY.

Thanks,

Daniel

Re: Date Format

YoussefBelloum — Wed, 07 Mar 2018 14:20:22 GMT

if your data sources are concatenated in one table, do like described above, otherwise you should use canonical date.

Canonical Date

But now we are far from the initial problem

Re: Date Format

rubenmarin — Wed, 07 Mar 2018 14:52:37 GMT

It case it helps:

=Date(Date#('APR-18','MMM-YY') , 'YYYYMM') => 201804; MM=Month

=Date(Date#('APR-18','MMM-YY') , 'YYYYmm') => 201800; mm=Minute