topic Re: Convert date filed? in QlikView

Convert date filed?

qlikuser222 — Mon, 22 May 2017 20:12:34 GMT

Hi All,

I have data in excel in below format

Period

1-Jan-2017

1-Feb-2017

1-Mar-2017

when I load in Qlik using below function but it throws null, I am not sure what is the problem. When I use the the same formula on front end it works .

Load

date(date#(Period,'M/D/YYYY'),'DD/MM/YYYY') as Period // gives nothing but blanks

I want to show Period as 01/01/2017, 01/02/2017, 01/03/2017.

Can you please help me?

Thanks

Re: Convert date filed?

Peter_Cammaert — Mon, 22 May 2017 20:16:41 GMT

How do you import your date values from Excel? By reading an .xls or xlsx file? Then it's pretty simple. Together with the string representations, Excel stores the same underlying DateTime binary values as QlikView does, and QV will recognize them immediately. No need to interprete the text values, because they're just ... text.

Just format them into the display format you like.

Re: Convert date filed?

qlikuser222 — Mon, 22 May 2017 20:22:00 GMT

Thanks for your reply,

but how can I convert

1-Jan-2017

1-Feb-2017

1-Mar-2017

From excel to

01/01/2017

01/02/2017

01/03/2017

in Qlik?

Re: Convert date filed?

Digvijay_Singh — Mon, 22 May 2017 20:24:57 GMT

If you can share sample Excel, it would help. The expression you shared is not interpreting source format as per the values you have shared, Did u try this? -

date(date#(Period,'D-MMM-YYYY'),'DD/MM/YYYY') as Period

Re: Convert date filed?

Peter_Cammaert — Mon, 22 May 2017 20:26:54 GMT

Either set your default format to something like 'MM/DD/YYYY' (top of your load script) or use a Date() function to format the field string represntation when loading columns from Excel. Like in

LOAD ...

Date(MyExcelDateField, 'MM/DD/YYYY') AS MyExcelDateField,

...

FROM MyExcelFile.xls (options);

If this doesn't work as-is, please post a data sample in an Excel file in this thread.