topic Re: Convert Mon'YY to MM/DD/YY. in QlikView

Convert Mon'YY to MM/DD/YY.

Anonymous — Mon, 11 Apr 2016 11:36:58 GMT

Hi All,

Convert Mon'YY to MM/DD/YY.

Ex: April'15 to 04/01/15.

Thanks,

Krishna

Month
April'15
Aug'15
Dec'15
Feb'16
Jan'16
July'15
June'15
May'15
Nov'15
Oct'15
Sept'15

Re: Convert Mon'YY to MM/DD/YY.

Chanty4u — Mon, 11 Apr 2016 11:38:47 GMT

try

=Date(Date#(Month,'MMM''YY'), MM/DD/YY) as NewMonth

Re: Convert Mon'YY to MM/DD/YY.

awhitfield — Mon, 11 Apr 2016 11:39:00 GMT

Have you posted the wrong xlsx, there are no dates in it!

Andy

Re: Convert Mon'YY to MM/DD/YY.

sunny_talwar — Mon, 11 Apr 2016 11:39:10 GMT

I don't see Month field in your attached Excel

Re: Convert Mon'YY to MM/DD/YY.

Anonymous — Mon, 11 Apr 2016 11:44:46 GMT

Hi Sunny,

I'm Sorry. Just now I attached 1.Xlx. Please delete attached file.

Thanks,

Krishna

Re: Convert Mon'YY to MM/DD/YY.

Kushal_Chawda — Mon, 11 Apr 2016 11:45:45 GMT

try this

date(monthstart(date#(purgechar(Month,chr(39)),'MMMYY')),'MM/DD/YY') as Month

Re: Convert Mon'YY to MM/DD/YY.

tresB — Mon, 11 Apr 2016 12:06:30 GMT

Alt(Date(Date#(String2, 'MMMM''YY'), 'MM/DD/YY'),

Date(Date#(String2, 'MMM''YY'), 'MM/DD/YY')) as Date

Note: Single quote twice (in red)

Replace 'String2' with your field name.

Re: Convert Mon'YY to MM/DD/YY.

senpradip007 — Mon, 11 Apr 2016 12:12:21 GMT

Try like

LOAD *,

MakeDate(Year(New), Month(New), 1) as New1;

LOAD *,

date#(replace(Month, Chr(39), ''), 'MMMYY') as New Inline [

Month

Apr'15

Aug'15

Dec'15

Feb'16

Jan'16

Jul'15

Jun'15

May'15

Nov'15

Oct'15

Sep'15

];

Re: Convert Mon'YY to MM/DD/YY.

sunny_talwar — Mon, 11 Apr 2016 12:17:34 GMT

Here is another option:

SET MonthNames='Jan;Feb;Mar;April;May;June;July;Aug;Sept;Oct;Nov;Dec';

Table:

LOAD Date(Date#(Month, 'MMM''YY'), 'MM/DD/YYYY') as MonthYear,

Month

FROM

[1.xls]

(biff, embedded labels, table is Sheet1$);

Re: Convert Mon'YY to MM/DD/YY.

Chanty4u — Mon, 11 Apr 2016 12:22:38 GMT

Did u try my expression?

Re: Convert Mon'YY to MM/DD/YY.

Mon, 11 Apr 2016 12:27:45 GMT

Please check excel is this is the excel your getting because month is different formation

example:May'15

April'15

sept'15

there are the different formats in excel

if all are mmmyy formation then Kushal condition will work

Re: Convert Mon'YY to MM/DD/YY.

sunny_talwar — Mon, 11 Apr 2016 12:29:35 GMT

Even if they are not the same, they can be forced to be MMMYY using the following set statement

SET MonthNames='Jan;Feb;Mar;April;May;June;July;Aug;Sept;Oct;Nov;Dec';

Re: Convert Mon'YY to MM/DD/YY.

Anonymous — Mon, 11 Apr 2016 12:41:27 GMT

Thanks Sunny.