Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
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'))
))

Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

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

Highlighted
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

Highlighted
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

Highlighted
Creator
Creator

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

Highlighted
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,