topic Re: Reading Dates in QlikView

Reading Dates

Thu, 05 Mar 2015 06:03:53 GMT

Experts ,

Many times I faced dates issues but none of old techniques worked. Could you please help me to read the dates here.

[Output]:

LOAD

ALT(DATE#(Date,'D/M/YYYY'),DATE#(Date,'DD/M/YYYY'),DATE#(Date,'D/MM/YYYY'),DATE#(Date,'DD/MM/YYYY'))as Date,

Sales

RESIDENT [Dress Sales] ;

But My Output is:

My Source is:

Re: Reading Dates

mightyqlikers — Thu, 05 Mar 2015 06:06:23 GMT

Hi,

Please share sample data.

samwise

Re: Reading Dates

Thu, 05 Mar 2015 06:09:56 GMT

Sample data is attached

Re: Reading Dates

avinashelite — Thu, 05 Mar 2015 06:11:45 GMT

Hi Guruprem,

Few of the data is store as numeric I guess that's why your facing the issues. Please post your app so that we can help you out.

Re: Reading Dates

mightyqlikers — Thu, 05 Mar 2015 06:16:03 GMT

Please find the attached qvw

Regards

Samwise

Re: Reading Dates

Thu, 05 Mar 2015 06:18:23 GMT

I am using PE, Could you post script?

Re: Reading Dates

fashid — Thu, 05 Mar 2015 06:23:00 GMT

Hi,

use this

Directory;

LOAD

Date,

Date(Date#(Date,'DD/MM/YYYY'),'DD-MM-YYYY') as DATES

FROM

[Sample Dates.xlsx]

(ooxml, embedded labels, table is Sheet1);

regards

nadeem

Re: Reading Dates

jonathandienst — Thu, 05 Mar 2015 06:23:06 GMT

I think its just a formatting issue. This works with your sample data:

Date(DATE#(Date,'DD/MM/YYYY'))as Date

HTH

Jonathan

Re: Reading Dates

Thu, 05 Mar 2015 06:26:46 GMT

I tried this, it output date is not showing some dates(Text dates as shown in first post )

Re: Reading Dates

Thu, 05 Mar 2015 06:28:48 GMT

I tried this, it output date is not showing some dates(Text dates as shown in first post) . I mean there should be 24 dates in output but only 14 dates are showing

Re: Reading Dates

fashid — Thu, 05 Mar 2015 06:30:41 GMT

Hi Guruprem,

do you have these dates somewhere else also in the application.

This could be a result of cancatenation of dates further in the script where you may not have formatted the date.

If not preferably attach your app.

Regards,

Nadeem

Re: Reading Dates

jonathandienst — Thu, 05 Mar 2015 06:40:24 GMT

Try it on the sample you posted. It works there. So it looks like your problem is somewhere else in your data.

Re: Reading Dates

Thu, 05 Mar 2015 06:46:44 GMT

Hi Guruprem,

Can you please either change the Date format in excel - DD/MM/YYYY to M/D/YYYY or change qlikview default date format to D/M/YYYY ?

SET DateFormat='D/M/YYYY';

While reading data from excel , qlikview has converted valid date formats to number and others are strings. I don't know whether is other solutions, haven't tried it myself

Thanks & Regards,
Vipin

Re: Reading Dates

avinashelite — Thu, 05 Mar 2015 06:57:37 GMT

use this it will work:

=if(IsNum([created Date])=-1,Date(DATE#([created Date],'DD/MM/YYYY')),Date(Date#([created Date],'DD/MM/YYYY'),'DD/MM/YYYY'))

Re: Reading Dates

Thu, 05 Mar 2015 07:40:40 GMT

Confident about this , let me check

Re: Reading Dates

Thu, 05 Mar 2015 07:41:28 GMT

Tried, Result is same ., Issue still persists

Re: Reading Dates

Thu, 05 Mar 2015 08:15:15 GMT

Did u change the format in excel or in qlikview??/

Re: Reading Dates

jagan — Thu, 05 Mar 2015 08:15:54 GMT

Hi,

Try like this

LOAD

Alt(Date(DATE#([created Date],'D/M/YYYY')), Date([created Date])) AS Formatted_Date

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Reading Dates

Thu, 05 Mar 2015 09:43:49 GMT

Even this led to the only 11 dates in o/p where as I have 24 dates in Excel

Re: Reading Dates

Thu, 05 Mar 2015 09:45:13 GMT

Even this led to the only 11 dates in o/p where as I have 24 dates in Excel