topic Re: Date Format Issue.. in QlikView

Date Format Issue..

Srinivas — Tue, 27 May 2014 16:47:04 GMT

Hi Folks,

I have excel in that date have two formats so qlikview shows two different formats,

But i want in one format like 'MM/DD/YYYY' , Could you please help on this.

this format i want....

Regards,

Munna

Re: Date Format Issue..

Tue, 27 May 2014 17:00:28 GMT

Not sure I fully understand what you're asking, because the dates in the excel seem to have the same format...If you just want to include the leading zero values for the single digit dates something like the expression below should work.

date(date#(YourDateField, 'M/D/YYYY'), 'MM/DD/YYYY')

Re: Date Format Issue..

ashfaq_haseeb — Tue, 27 May 2014 17:01:04 GMT

Hi may be like this.

LOAD

Alt(Date#(Date, 'M/D/YYYY'), Date#(Date, 'DD/MM/YYYY'), Date#(Date, 'MM/DD/YYYY'), Date#(Date, 'YYYY-MM-DD'), Date#(Date, 'D/M/YYYY'), 'Invalid Date') as ValidatedDate,

Date,

[Sales Quantity]

FROM

(ooxml, embedded labels, table is Sheet1);

Regards

ASHFAQ

Re: Date Format Issue..

its_anandrjs — Tue, 27 May 2014 17:04:52 GMT

You have to use Alt function see the example

LOAD

Date,

Alt( Date(date#( Date , 'M/DD/YYYY' ), 'MM/DD/YYYY'),

Date(date#( Date , 'DD/MM/YYYY' ), 'DD/MM/YYYY')) AS NewDate,

[Sales Quantity]

FROM

[22052014\DateIssue.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Date Format Issue..

its_anandrjs — Tue, 27 May 2014 17:16:55 GMT

Update the script now check

See the below load script that contains the Alt script use in the load script

LOAD

Date,

Alt( Date(Date#( Date , 'M/D/YYYY' ),'MM/DD/YYYY'),

Date(Date#( Date , 'MM/D/YYYY'),'MM/DD/YYYY'),

Date(Date , 'MM/DD/YYYY')) AS NewDate,

[Sales Quantity]

FROM

[DateIssue.xlsx]

(ooxml, embedded labels, table is Sheet1);

And you get the below field

Re: Date Format Issue..

Srinivas — Tue, 27 May 2014 17:28:29 GMT

it's not working..i want to see all dates..

All should be same format...

Re: Date Format Issue..

its_anandrjs — Tue, 27 May 2014 17:31:29 GMT

Update now check the script

Try this one

Alt( Date(Date#( Date , 'M/D/YYYY' ),'MM/DD/YYYY'),

Date(Date#( Date , 'MM/D/YYYY'),'MM/DD/YYYY'),

Date(Date , 'MM/DD/YYYY')) AS NewDate,

Re: Date Format Issue..

Srinivas — Tue, 27 May 2014 17:41:32 GMT

Hi Anand,

In your screen showed correct but when i tried sam code in my script it showed wrong...see below screen.

But i want all date should be 2/3/2014.. like way

Could you please help on this..

Advance & Thanks

Munna.

Re: Date Format Issue..

its_anandrjs — Tue, 27 May 2014 17:47:41 GMT

I update the above post now check with the script that is.

Alt( Date(Date#( Date , 'M/D/YYYY' ),'MM/DD/YYYY'),

Date(Date#( Date , 'MM/D/YYYY'),'MM/DD/YYYY'),

Date(Date , 'MM/DD/YYYY')) AS NewDate,

Re: Date Format Issue..

Srinivas — Wed, 28 May 2014 03:15:35 GMT

Thank you a lot Mr.Anand, It's working now.

Regards,

Munna