Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Thanks
Hi,
Try this
if(IsText(CreatedDate),Date(Date#(CreatedDate,'MM/DD/YYYY hh:mm'),'DD-MMM-YYYY'), CreatedDate) as CreatedDate,
Thanks,
Pooja
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
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'))
))
That unfortunately made all the fields null with nothing generating the field whatsoever
Yeah weirdly I've tried it in Excel and it doesn't change them either
Hi,
Try this
if(IsText(CreatedDate),Date(Date#(CreatedDate,'MM/DD/YYYY hh:mm'),'DD-MMM-YYYY'), CreatedDate) as CreatedDate,
Thanks,
Pooja
Try this
Date(Alt(Date(CreateDate, 'DD-MMM-YYYY'),Date#(CreateDate, 'MM/DD/YYYY hh:mm')),'DD-MMM-YYYY')
Regards
Ahmar
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)?
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,