Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

Convert date filed?

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

1 Solution

Accepted Solutions

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.

View solution in original post

4 Replies

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.

Creator
Creator

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?

Master III
Master III

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

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.

View solution in original post