topic Re: Need help in QlikView

Need help

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Need help

settu_periasamy — Mon, 02 May 2016 15:26:16 GMT

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

Re: Need help

Gysbert_Wassenaar — Mon, 02 May 2016 15:30:46 GMT

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

Re: Need help

Tue, 03 May 2016 04:20:53 GMT

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??

Re: Need help

Gysbert_Wassenaar — Tue, 03 May 2016 12:57:58 GMT

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?

Re: Need help

settu_periasamy — Wed, 04 May 2016 05:33:56 GMT

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