Date pulled from excel is neglecting Year

[divya_anand]

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.

[imrasyed]

Hi,

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

[techvarun]

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

[divya_anand]

Hi Varun,

Can you please try it with this file?

[divya_anand]

Hi Avinash,

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

[divya_anand]

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.

[Anil_Babu_Samineni]

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

[divya_anand]

Hi Anil,

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

[Anil_Babu_Samineni]

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

[Anil_Babu_Samineni]

Attached 2 methods

[divya_anand]

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.