Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
divya_anand
Contributor II

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.

Tags (3)
24 Replies

Re: Date pulled from excel is neglecting Year

You can do

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

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
Contributor II

Re: Date pulled from excel is neglecting Year

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

This?

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

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)
techvarun
Valued Contributor II

Re: Date pulled from excel is neglecting Year

May be a sample data will help here

divya_anand
Contributor II

Re: Date pulled from excel is neglecting Year

Hi Varun,

Here it is.

Re: Date pulled from excel is neglecting Year

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

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

Re: Date pulled from excel is neglecting Year

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

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

antoniotiman
Honored Contributor III

Re: Date pulled from excel is neglecting Year

In Your file Date format is DD/MM/YYYY

So

LOAD Date

From .......