topic Re: Date format issue in QlikView

Date format issue

arpitkharkia — Wed, 31 Jan 2018 11:41:37 GMT

Hi all,

PFA the qlikview app. I want the dimension YearMonth as Jan-2017, Feb-2017 from 12017, 22017 and so on.

Thanks for your help!

Re: Date format issue

tresB — Wed, 31 Jan 2018 11:46:43 GMT

Try:

=Date(Date#(YearMonth,'MYYYY'),'MMM-YYYY')

Re: Date format issue

kaushiknsolanki — Wed, 31 Jan 2018 11:46:52 GMT

Try this,

Load field, date(date#(field,'MYYYY'),'MMM-YYYY') as newfield

From xyz

Regards,

Kaushik Solanki

Re: Date format issue

prma7799 — Wed, 31 Jan 2018 11:49:48 GMT

Try this

Table:

LOAD * INLINE [

YearMonth

12017

22017

32017

42017

52017

62017

72017

82017

92017

102017

112017

122017

];

Load *,

Year(Date#(YearMonth,'[]YYYY')) as Year,

Date(Date#(YearMonth,'MYYYY'),'MMM-YYYY') as NewYearMonth

Resident Table;

Re: Date format issue

arpitkharkia — Wed, 31 Jan 2018 11:54:00 GMT

I followed the same approach. The issue is highlighted below

Re: Date format issue

arpitkharkia — Wed, 31 Jan 2018 11:57:02 GMT

For cases 112017 and 122017 im getting Jan-12017 and Jan -22017. How to resolve this?

Re: Date format issue

tresB — Wed, 31 Jan 2018 11:57:43 GMT

Sorry, one 'M' was missed. Try:

Date(Date#(YearMonth,'MMYYYY'),'MMM-YYYY')

Re: Date format issue

arpitkharkia — Wed, 31 Jan 2018 12:01:50 GMT

This working for those where Month is 2 digits. Not for rest of the cases.

Re: Date format issue

prma7799 — Wed, 31 Jan 2018 12:02:25 GMT

Try this

Table:

LOAD * INLINE [

YearMonth

012017

022017

032017

042017

052017

062017

072017

082017

092017

102017

112017

122017

];

Load *,

Year(Date#(YearMonth,'[]YYYY')) as Year,

Date(Date#(YearMonth,'MMYYYY'),'MMM-YYYY') as NewYearMonth

Resident Table;

Re: Date format issue

sannidhikumar — Wed, 31 Jan 2018 12:02:33 GMT

Try this Arpita.

Date(Date#(YearMonth,'MMMYYYY'),'MMM-YYYY') AS YearMonth

Re: Date format issue

arpitkharkia — Wed, 31 Jan 2018 12:05:56 GMT

But buddy im getting it in that format. Should i use len() or other string functions?

Re: Date format issue

sergio0592 — Wed, 31 Jan 2018 12:06:09 GMT

Try with :

Load *,
if(len(YearMonth)=5, date(date#(YearMonth,'MYYYY'),'MMM-YYYY'), date(date#(YearMonth,'MMYYYY'),'MMM-YYYY')) as newfield
Resident Table;

Re: Date format issue

arpitkharkia — Wed, 31 Jan 2018 12:06:24 GMT

Should i use len() or other string functions?

Re: Date format issue

prma7799 — Wed, 31 Jan 2018 12:08:42 GMT

Use if condition with len function Like Jean said

if(len(YearMonth)=5, date(date#(YearMonth,'MYYYY'),'MMM-YYYY'), date(date#(YearMonth,'MMYYYY'),'MMM-YYYY')) as Newfield

Re: Date format issue

tresB — Wed, 31 Jan 2018 12:09:49 GMT

You are right. Try this:

Date(Date#(Num(YearMonth,'000000'),'MMYYYY'),'MMM-YYYY') as "Month Year"

Re: Date format issue

ElizaF — Wed, 31 Jan 2018 12:24:54 GMT

Another solution is:

Table:

LOAD

Date(MakeDate(right(YearMonth,4),left(YearMonth,Len(YearMonth)-4)),'MMM YYYY') as YearMonth

;

LOAD * INLINE [

YearMonth

12017

22017

32017

42017

52017

62017

72017

82017

92017

102017

112017

122017

];