Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mikel_de
New Contributor III

Date Field and Load Expression

Hello!

I have some problems with the data preparation. Normally, when I load an excel file, Qlik Sense lets me define that a particular column contains dates like this:

Capture.PNG

However, I have just loaded an excel file that contains four sheets (data for Jan, Feb, Mar, and Apr and all of them with the exact same structure and column names) and this option is not available:

Capture2.PNG

What could be the problem?

My second question is if there is any way to create a general Load Expression using the Data Load Editor. I would like to change the column names of all four sheets and if it is possible this expression should also work for the next files I'm about to load soon (May and Jun).

Thanks!

Best,

Mikel

1 Solution

Accepted Solutions

Re: Date Field and Load Expression

The reason will be that this field won't be interpreted as a date-field else as a string. This could be solved by converting this field like:

date(date#(Delivery_Date, 'DD.MM.YYYY'), 'DD.MM.YYYY')

whereby you need to specify the correct format-pattern.

- Marcus

6 Replies

Re: Date Field and Load Expression

The reason will be that this field won't be interpreted as a date-field else as a string. This could be solved by converting this field like:

date(date#(Delivery_Date, 'DD.MM.YYYY'), 'DD.MM.YYYY')

whereby you need to specify the correct format-pattern.

- Marcus

mikel_de
New Contributor III

Re: Date Field and Load Expression

Great, great, I did it like this and also copied the autoCalendar from another app so now everything seems to be ok. Thank you!

However, I had to convert the date fields for all four worksheets one by one, which brings me back to my second question - is it possible to do this with one general expression in the Data Load Editor?

Re: Date Field and Load Expression

Yes, it was meant for the script:

...

date(date#(Delivery_Date, 'DD.MM.YYYY'), 'DD.MM.YYYY') as Delivery_Date

...

- Marcus

mikel_de
New Contributor III

Re: Date Field and Load Expression

Okay, then I apparently have some trouble understanding what to do exactly.

When I open the Data Load Editor, I have two tabs there  - Main and Auto-generated section. What I did, was to unlock the Auto-generated section and replace the automatically generated Date entries with the expression you suggested. I have four worksheets in my excel file so I had to replace the Date entry for each sheet individually. Where and how should I insert this expression so that I only do I once?

Re: Date Field and Load Expression

There are several ways possible to solve such a case:

- you replaced your field with the expression for each sheet (the change is only once - the rest is just copy + paste)

- you load your sheets within a loop, for example:

  Loading Multiple Excel Sheets Dynamically along with file name and sheet name

- afterwards your excel-loading you could load the table again and change there your field

- very probably some more ...

The easiest way would be the first one, the loop one is the most elegant way (which I would use) and I would consider the third suggestion if there are some more transformations necessary.

- Marcus

mikel_de
New Contributor III

Re: Date Field and Load Expression

Thank you!