Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help

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

5 Replies
settu_periasamy
Master III
Master III

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
Partner - Champion III
Partner - Champion III

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


talk is cheap, supply exceeds demand
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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?


talk is cheap, supply exceeds demand
settu_periasamy
Master III
Master III

Hi,

You can give the format in the Date function like

Date(AltDOJ,
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