topic Re: Date format?? in QlikView

Date format??

qlikuser222 — Mon, 17 Jul 2017 14:45:40 GMT

Hi everyone,

I have a file where the date format is in both MM/DD and DD/MM.

I want to pull the file in Qlik with DD/MM format but it is not as expected.

PFA and please help me.

Thanks

Re: Date format??

effinty2112 — Mon, 17 Jul 2017 14:54:09 GMT

Hi Nicholas,

Try:

LOAD

Date(Data,'DD/MM') as Data,

Status

FROM

test.xlsx

Good luck

Andrew

Re: Date format??

qlikuser222 — Mon, 17 Jul 2017 15:02:18 GMT

HIm

Thanks for reply.

Qlik is reading 01/06 (Jan ,6th) as May 1st.

This is because inconsistency in the Data column in excel file. How can we fix that?

Thanks

Re: Date format??

sunny_talwar — Mon, 17 Jul 2017 15:23:56 GMT

I am seeing 1/6/2017 as Jan 6th only...

Screenshot is from your attached application.... I just changed the date format on the numbers tab

Re: Date format??

qlikuser222 — Mon, 17 Jul 2017 15:28:59 GMT

Thanks for reply,

How about the

13/03/2017 Qlik is not considering its as Date.

I want to show it as Mar/13/2017

Thanks

Re: Date format??

effinty2112 — Mon, 17 Jul 2017 15:30:51 GMT

Hi Nicholas,

When you look at your data can you tell if a value is formatted one way or the other? If something about another field can be used as a condition to tell QV how to format it then you could have something like

if(condition is met, Date(Data,'DD/MM') ,Date(Data,'MM/DD')) as Data

Cheers

Andrew

Re: Date format??

sunny_talwar — Mon, 17 Jul 2017 15:31:48 GMT

Try using this before you load your table

SET DateFormat='DD/MM/YYYY';

Re: Date format??

sunny_talwar — Mon, 17 Jul 2017 15:32:39 GMT

SET DateFormat='DD/MM/YYYY';

LOAD Data,

Date(Data) as Date,

Status

FROM

[test.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Date format??

stigchel — Mon, 17 Jul 2017 15:43:47 GMT

I seriously doubt the integrity of the data, even if you format certain dates differently, you still end up with dates in e.g. december 2017 with a status complete. If this is incorrect then I see no way of correcting that with the current data.