topic Re: Convertion of diff date format in to unique format in QlikView

Convertion of diff date format in to unique format

Thu, 28 Mar 2013 09:52:09 GMT

Hi All,

I have date field in the attached excel. it is in DD-MM-YYYY and in string format. i need to convert everything in to DD-MM-YYYY format.

For Instance in the attached excel i have 01-08-2013, 01-09-2013 and some string dates like 15/01/2013 , 16/01/2013 but all are belongs to january month.

Expected result is 08-01-2013, 09-01-2013, 15-01-2013, 16-01-2013

Kindly help me to resolve this.

Thanks in Advance.

Mahi.

Re: Convertion of diff date format in to unique format

Gysbert_Wassenaar — Thu, 28 Mar 2013 09:57:15 GMT

See here for a solution

Re: Convertion of diff date format in to unique format

Thu, 28 Mar 2013 10:05:13 GMT

Thanks Gysbert,

But again am facing an issue, am getting dates for strings not for num. for instance in attached excel i have

01-01-2013, 01-02-2013, 01-08-2013, 01-09-2013, 01-10-2013.

But am not getting this values. kindly help me to get this data also in DD-MM-YYYY format.

Thanks in Advance.

Mahi.

Re: Convertion of diff date format in to unique format

Thu, 28 Mar 2013 10:18:53 GMT

Try this

Hope you are looking this

Date(makedate(right(fieldname,4),mid(fieldname,4,1),subfield(fieldname,'/',1)),"DD-MM-YYYY")

Regards,

Amay

Re: Convertion of diff date format in to unique format

Thu, 28 Mar 2013 10:19:26 GMT

Actually In excel, the remaining dates are in dd-mm-yyyy format but i want that in mm-dd-yyyy format.

I Mean all the dates ( 01-01-2013, 01-02-2013, 01-08-2013, 01-09-2013, 01-10-2013 )belongs to January Month.

Kindly help me to resolve this.

Thanks in Advance.

Mahi

Re: Convertion of diff date format in to unique format

Thu, 28 Mar 2013 10:20:25 GMT

As per my understanding you required to convert string to Date format then try this

Date(Date#(DateField,'DD/MM/YYYY'),'DD-MM-YYYY')

sorry if i'm wrong in understanding

Best of luck

Re: Convertion of diff date format in to unique format

manideep78 — Thu, 28 Mar 2013 10:23:28 GMT

Hi see below code

LOAD Date(Date#(A,'DD/MM/YYYY'),'DD-MM-YYYY') as A,

FROM

C:\Users\Manideep\Downloads\comm_excel.xlsx

(ooxml, no labels, table is Sheet1);

A	B
18-01-2013	AAA
22-01-2013	AAA
28-01-2013	AAA
16-01-2013	AAA
31-01-2013	AAA
15-01-2013	AAA
30-01-2013	AAA
	AAA
	BBB

Re: Convertion of diff date format in to unique format

Thu, 28 Mar 2013 10:43:21 GMT

Hi Manideep and satish, thanks for your reply. This is ok, am getting string format dates as date format. but few dates i have in DD-MM-YYYY ( 01-01-2013, 01-02-2013, 01-08-2013, 01-09-2013, 01-10-2013 ) format i need to convert that in to MM-DD-YYYY format. i need to convert all the above mentioned dates belongs to JAN month.

Thanks in Advance,

Mahi.

Re: Convertion of diff date format in to unique format

manideep78 — Thu, 28 Mar 2013 10:51:37 GMT

try this

LOAD if(Month(Date(Date#(A,'DD/MM/YYYY'),'DD-MM-YYYY'))=1,Date(Date#(A,'DD/MM/YYYY'),'MM-DD-YYYY'),Date(Date#(A,'DD/MM/YYYY'),'DD-MM-YYYY')) as A,

FROM

C:\Users\Manideep\Downloads\comm_excel.xlsx

(ooxml, no labels, table is Sheet1);

Re: Convertion of diff date format in to unique format

Thu, 28 Mar 2013 11:17:18 GMT

Hi Manideep,

Thanks for your effort, again am not getting the data ( 01-01-2013, 01-02-2013, 01-08-2013, 01-09-2013, 01-10-2013 ) in that date field.

I need to include that data also which are in date month year format ( i need to convert that into month date year)

Thanks in Advance,

Mahi

Re: Convertion of diff date format in to unique format

manideep78 — Thu, 28 Mar 2013 11:29:55 GMT

LOAD date(Alt(Date#(A,'DD/MM/YYYY'),Date#(A,'DD-MM-YYYY'),Date#(A,'MM-DD-YYYY'),Date#(A,'MM/DD/YYYY'))) as A,

FROM

C:\Users\Manideep\Downloads\date.xlsx

(ooxml, no labels, table is Sheet1);