Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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??
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?
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