topic Re: Forcing Date in script in QlikView

Forcing Date in script

khaycock — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Forcing Date in script

tomasz_tru — Wed, 05 Sep 2018 16:41:35 GMT

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

Re: Forcing Date in script

vamsee — Wed, 05 Sep 2018 18:58:15 GMT

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

Re: Forcing Date in script

khaycock — Thu, 06 Sep 2018 10:02:28 GMT

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

Re: Forcing Date in script

khaycock — Thu, 06 Sep 2018 10:02:47 GMT

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

Re: Forcing Date in script

pooja_prabhu_n — Thu, 06 Sep 2018 10:15:16 GMT

Hi,

Try this

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

Thanks,

Pooja

Re: Forcing Date in script

ahmar811 — Thu, 06 Sep 2018 10:15:28 GMT

Try this

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

Regards

Ahmar

Re: Forcing Date in script

khaycock — Thu, 06 Sep 2018 10:39:26 GMT

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

Re: Forcing Date in script

pooja_prabhu_n — Thu, 06 Sep 2018 10:55:28 GMT

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,