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: 
khaycock
Creator
Creator

Forcing Date in script

I'm loading in an Excel file that has data set as date but for some reason, not all fields format, and this is then shown in the dashboard, even after forcing them as a date in properties.

Any idea on how I can force these dates to all be exactly the same?

date.PNG

Thanks

1 Solution

Accepted Solutions
pooja_prabhu_n
Creator III
Creator III

Hi,

Try this

if(IsText(CreatedDate),Date(Date#(CreatedDate,'MM/DD/YYYY hh:mm'),'DD-MMM-YYYY'), CreatedDate) as CreatedDate,

Thanks,

Pooja

View solution in original post

8 Replies
tomasz_tru
Specialist
Specialist

I bet some of the fields are actually text fields. You can change them into dates in Excel using date function or in the load script.

Tomasz

vamsee
Specialist
Specialist

Sorry Missed out the third type you have

Try

Date(
Alt(
Date#(CreateDate, 'MM/DD/YYYY'),
Date(Date#(CreateDate, 'MM/DD/YYYY hh:mm:ss'))
,
Date(Date#(CreateDate, 'DD-MMM-YY'))
))

khaycock
Creator
Creator
Author

That unfortunately made all the fields null with nothing generating the field whatsoever

khaycock
Creator
Creator
Author

Yeah weirdly I've tried it in Excel and it doesn't change them either

pooja_prabhu_n
Creator III
Creator III

Hi,

Try this

if(IsText(CreatedDate),Date(Date#(CreatedDate,'MM/DD/YYYY hh:mm'),'DD-MMM-YYYY'), CreatedDate) as CreatedDate,

Thanks,

Pooja

ahmar811
Creator III
Creator III

Try this

Date(Alt(Date(CreateDate, 'DD-MMM-YYYY'),Date#(CreateDate, 'MM/DD/YYYY hh:mm')),'DD-MMM-YYYY')


Regards

Ahmar

khaycock
Creator
Creator
Author

Do you have any idea on how I can force either the excel file or qlikview to make it a standard date? I'm now having problems with it reading both UK and US date formats (making dates that should be read as 12-Jul-2018 and 07-Dec-2018 instead)?

pooja_prabhu_n
Creator III
Creator III

Hi,

Did you mean all the dates in 'DD-MMM-YYYY' format?

if(IsText(CreatedDate),Date(Date#(CreatedDate,'MM/DD/YYYY hh:mm'),'DD-MMM-YYYY'), Date(CreatedDate,'DD-MMM-YYYY')) as CreatedDate,