topic Re: Alt Doubt in QlikView

Alt Doubt

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Alt Doubt

sunny_talwar — Fri, 30 Sep 2016 14:35:59 GMT

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);

Re: Alt Doubt

Fri, 30 Sep 2016 14:50:53 GMT

Thanks Sunny for quick response!

I tried as you suggested

but still am getting same output

please suggest

Re: Alt Doubt

sunny_talwar — Fri, 30 Sep 2016 14:56:37 GMT

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

Re: Alt Doubt

Fri, 30 Sep 2016 16:14:34 GMT

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

Re: Alt Doubt

sunny_talwar — Fri, 30 Sep 2016 16:20:08 GMT

Your Excel doesn't have it right

Re: Alt Doubt

rupamjyotidas — Fri, 30 Sep 2016 16:22:58 GMT

Check what you have define here under 'Number'

Re: Alt Doubt

Fri, 30 Sep 2016 16:57:36 GMT

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';

Re: Alt Doubt

sunny_talwar — Fri, 30 Sep 2016 16:59:28 GMT

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?

Re: Alt Doubt

Fri, 30 Sep 2016 17:10:03 GMT

Sunny will you be elaborate little bit more?

How do we change ?

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

Re: Alt Doubt

sunny_talwar — Fri, 30 Sep 2016 17:12:21 GMT

Not sure which version of Excel you have, but this is what I have:

Re: Alt Doubt

Fri, 30 Sep 2016 17:31:16 GMT

Yes Sunny I got it it showing as Wednesday,october 05, 2016 .

But I wanted to know why it didn't recognise 10/5/2016 as 10-May-2016?

Re: Alt Doubt

sunny_talwar — Fri, 30 Sep 2016 17:33:51 GMT

I guess store it as 5/10/2016 in your Excel because Excel's inbuilt format is MM/DD/YYYY instead of DD/MM/YYYY

Re: Alt Doubt

Fri, 30 Sep 2016 17:41:04 GMT

Only problem with 10/5/2016

It works fine for 29/2/2016 and showing 29-Feb-2016

So then how do we change? Do you have any solution for this?

Re: Alt Doubt

sunny_talwar — Fri, 30 Sep 2016 17:46:12 GMT

I told you, you will have to fix your dates in the Excel file

Re: Alt Doubt

Fri, 30 Sep 2016 17:48:07 GMT

Only problem with 10/5/2016

It works fine for 29/2/2016 and showing 29-Feb-2016

I don't know where I went wrong exactly!

Re: Alt Doubt

sunny_talwar — Fri, 30 Sep 2016 17:49:53 GMT

Anywhere you have 12/12 or 11/11 where the first thing can be Month or date, it won't probably read it correctly

Re: Alt Doubt

nagarjuna_kotha — Fri, 30 Sep 2016 17:56:12 GMT

Hi Try this

LOAD id,

DAte(Date#(ord),'DD.MMM.YYYY') as Date,

Date(alt(date#(ord, 'YYYY-MM-DD'),date#(ord, 'MMM-DD-YYYY'),date(ord, 'M/D/YYYY'), date#(ord, 'DD-MM-YYYY'), date#(ord, 'DD/MM/YYYY'), 'No Date Found'),'DD.MM.YYYY') AS New,

sals,

desiredOP

FROM

(ooxml, embedded labels, table is Sheet1);

I hope this may helps you.

-Nagarjun