Date Format Issue..

[Srinivas]

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

[#2]

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')

[ashfaq_haseeb]

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

[its_anandrjs]

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);

[its_anandrjs]

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

[Srinivas]

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

All should be same format...

[its_anandrjs]

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,

[Srinivas]

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.

[its_anandrjs · Accepted solution]

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,

[Srinivas]

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

Regards,

Munna