topic Re: Date pulled from excel is neglecting Year in QlikView

Date pulled from excel is neglecting Year

divya_anand — Thu, 12 Oct 2017 06:11:15 GMT

Hi,

The source file that I am trying to load has a date field as shown below:

It clearly is displaying the date in a different format. when I pull this field into QlikView, for some reason it is not considering the year. I tried different combinations of Date & Date# but in vein.

How can I pull the date field in DD/MM/YYYY format without making any changes to the excel sheet?

Thank you.

Re: Date pulled from excel is neglecting Year

Anil_Babu_Samineni — Thu, 12 Oct 2017 06:16:21 GMT

You can do

Date(Date#(Date, 'Your Excel Format'), 'Required Format')

Re: Date pulled from excel is neglecting Year

divya_anand — Thu, 12 Oct 2017 06:28:41 GMT

Hi Anil,

Thank you for the response.

Ain't working. It is picking a dummy Year.

Correction, I am using a CSV file and not an excel, If that makes a difference.

Re: Date pulled from excel is neglecting Year

Anil_Babu_Samineni — Thu, 12 Oct 2017 06:36:15 GMT

This?

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

Re: Date pulled from excel is neglecting Year

techvarun — Thu, 12 Oct 2017 06:39:34 GMT

May be a sample data will help here

Re: Date pulled from excel is neglecting Year

divya_anand — Thu, 12 Oct 2017 06:43:32 GMT

Hi Varun,

Here it is.

Re: Date pulled from excel is neglecting Year

Anil_Babu_Samineni — Thu, 12 Oct 2017 06:54:13 GMT

What are you getting when you change environment to below

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';

Re: Date pulled from excel is neglecting Year

kavita25 — Thu, 12 Oct 2017 07:09:49 GMT

Try in a simple way..as in the excel file..the date format is MM/DD/YYYY.. so you just need to change it to DD/MM/YYYY

date(Date,'DD/MM/YYYY') as Date

Regards,

Kavita

Re: Date pulled from excel is neglecting Year

avinashelite — Thu, 12 Oct 2017 07:15:33 GMT

I tried with your data file , it didn't give any formatting issue ..share the actual will test

Re: Date pulled from excel is neglecting Year

antoniotiman — Thu, 12 Oct 2017 07:22:04 GMT

In Your file Date format is DD/MM/YYYY

LOAD Date

From .......

Re: Date pulled from excel is neglecting Year

imrasyed — Thu, 12 Oct 2017 07:38:02 GMT

Hi,

May be u need to change the SET DateFormat='MM/DD/YYYY';

Re: Date pulled from excel is neglecting Year

techvarun — Thu, 12 Oct 2017 07:55:48 GMT

The data loaded perfectly for me

Below are the environment variables i have used.

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

Re: Date pulled from excel is neglecting Year

divya_anand — Thu, 12 Oct 2017 08:38:09 GMT

Hi Varun,

Can you please try it with this file?

Re: Date pulled from excel is neglecting Year

divya_anand — Thu, 12 Oct 2017 08:39:29 GMT

Hi Avinash,

Maybe you can try this, it actually is a csv file.

Re: Date pulled from excel is neglecting Year

divya_anand — Thu, 12 Oct 2017 08:41:20 GMT

Thank you all for your response,

All your suggestions works on an excel sheet but not on a csv file. I missed out this very important point when I attached the excel earlier. Apologies.

Re: Date pulled from excel is neglecting Year

Anil_Babu_Samineni — Thu, 12 Oct 2017 08:44:51 GMT

Not sure, Data source is making wrong here(Because, If it runs in Excel it may run anywhere). What are you configure environment variables for Date format? Can we discuss this part ..

Re: Date pulled from excel is neglecting Year

divya_anand — Thu, 12 Oct 2017 08:50:21 GMT

Hi Anil,

Please give it a try with this QVW & CSV file.

Re: Date pulled from excel is neglecting Year

Anil_Babu_Samineni — Thu, 12 Oct 2017 09:03:20 GMT

Because, Qlikview Dates starts from 1899 and it loads only Month and date format due to Comma Seperate Values while data source is CSV.

1) Either you need to ask owner of CSV to set correcct format. Without there permission

2) Try this way

LOAD Date(Date#(Date(Date#(Date,'DD-MMM'),'DD-MM') & '-' & Year(Today()),'DD-MM-YYYY')) as Date

FROM

Data1.csv

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Re: Date pulled from excel is neglecting Year

Anil_Babu_Samineni — Thu, 12 Oct 2017 09:08:01 GMT

Attached 2 methods

Re: Date pulled from excel is neglecting Year

divya_anand — Thu, 12 Oct 2017 11:18:03 GMT

Hi Anil,

Thank you for the response.

Asking the data owner to change the format is not ideal because this file has to be exported on a daily basis and changing the format everyday is not a good idea.

And I see that you are extracting the Year from Today() which is not going to help us because we will have data from the next year (2018) too.