Need help

[#1]

HI Comunity,

Please Clarify this

I have a date column with multiple date formats and I tried with Alt function to convert into single standard format.

But am getting wrong.

Please have a look at

test1:

LOAD id,  

ename,   

pan,   

DOJ,   

sal,   

Date(Alt(Date#(DOJ,'D-MMM-YY'),

                Date#(DOJ,'DD-MM-YYYY'),

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

                Date#(DOJ,'YYYY-DD-MM'),'DD-MMM-YYYY')) as newdoj

FROM D:\Practice\JRD_New.xlsx (ooxml, embedded labels, table is Sheet1);

Any ideas please help

PFB for your reference

[settu_periasamy]

Maybe try this

Date(Alt(Date#(DOJ,'D-MMM-YY'),

                Date#(DOJ,'DD-MM-YYYY'),

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

                Date#(DOJ,'YYYY-DD-MM'),DOJ),'DD-MMM-YYYY') as newdoj

[Gysbert_Wassenaar]

Some values are already dates, so the date# function should not be used on those values. Try something like this:

Date(Alt(DOJ,

               Date#(DOJ,'D-MMM-YY'),

                Date#(DOJ,'DD-MM-YYYY'),

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

                Date#(DOJ,'D/M/YYYY'),

                Date#(DOJ,'YYYY-DD-MM'),'DD-MMM-YYYY')) as newdoj

[#4]

Thanks Gysbert,

Almost very close but format is different.

It shows 'D/MM/YY' instead of 'DD-MMM-YYYY'

To change this format I opted to SET variable and changed to 'DD-MMM-YYYY'  then it works fine.

But without modify in SET variable for date format is it possible to achieve??

[Gysbert_Wassenaar]

That's very weird since we explicitly give it the DD-MM-YYYY format:

Date(...do stuff..., 'DD-MM-YYYY') as newdoj

Can you post a small Qlikview document that demonstrates this issue?

[settu_periasamy]

Hi,

You can give the format in the Date function like

Date(Alt(  DOJ,
Date#(DOJ,'D-MMM-YY'),
Date#(DOJ,'DD-MM-YYYY'),
Date#(DOJ,'DD/MM/YYYY'),
Date#(DOJ,'YYYY-DD-MM')),
     'DD-MM-YYYY') as newdoj