Date Field and Load Expression

[mikel_de]

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:

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:

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

[marcus_sommer · Accepted solution]

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]

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?

[marcus_sommer]

Yes, it was meant for the script:

...

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

...

- Marcus

[mikel_de]

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?

[marcus_sommer]

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]

Thank you!