Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
divya_anand
Creator III
Creator III

Date pulled from excel is neglecting Year

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.

24 Replies
imrasyed
Partner - Creator II
Partner - Creator II

Hi,

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

techvarun
Specialist II
Specialist II

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
Creator III
Creator III
Author

Hi Varun,

Can you please try it with this file?

divya_anand
Creator III
Creator III
Author

Hi Avinash,

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

divya_anand
Creator III
Creator III
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
divya_anand
Creator III
Creator III
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

Attached 2 methods

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
divya_anand
Creator III
Creator III
Author

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.