Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Alt Doubt

Hi,

I need your help on my script to change the date formate to one standard format

my script is looks like as below

temp2:

LOAD id,

     ord,

     Date(Alt(

     Date(ord),

     Date#(ord,'DD/M/YYYY'),

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

     Date#(ord,'DD-MMM-YY'),

     Date#(ord,'MMM-DD-YYYY')),

     'DD-MMM-YYYY') as desiredOP,

      sals

FROM

D:<\path>abc.xlsx

(ooxml, embedded labels, table is Sheet1);

I haven't get my desiredOP

PFB for your reference.

Thanks in adv!

17 Replies
sunny_talwar

See if this works:

Table:

LOAD id,

    ord,

    sals,

    Date(Alt(

    Date(ord),

    Num(Date#(ord,'DD/M/YYYY')),

    Num(Date#(ord,'DD/MM/YYYY')),

    Num(Date#(ord,'DD-MMM-YY')),

    Num(Date#(ord,'MMM-DD-YYYY'))),

    'DD-MMM-YYYY') as desiredOP

FROM

Alt_Comunty.xlsx

(ooxml, embedded labels, table is Sheet1);


Capture.PNG

Not applicable
Author

Thanks Sunny for quick response!

I tried as you suggested

but still am getting same output

please suggest

sunny_talwar

The desiredOP in the above image is not what you wanted?

Not applicable
Author

In source It contains 10/5/2016 and expected output is 10-May-2016 but in my application It showing 5-Oct-2016


I don't know where I went wrong. Pls suggest

sunny_talwar

Your Excel doesn't have it right

Capture.PNG

rupamjyotidas
Specialist
Specialist

Check what you have define here under 'Number'

Not applicable
Author

No Sunny, In My excel it showing 10/5/2016 only!

But Once we loaded into qvw application It showing 05-Oct-2016

and also initially date format we have SET DateFormat='DD-MMM-YYYY';

sunny_talwar

10/05/2016 is interpreted as Oct-5th by your Excel. Can you try to convert that to long format in your Excel to check?

Not applicable
Author

Sunny will you be elaborate little bit more?

How do we change ?

(Rightclik>Format cells> date/custom>?)