topic Re: Load fields containing a date in QlikView

Load fields containing a date

Anonymous — Thu, 18 Oct 2018 07:48:15 GMT

Hello,

I want to load a table whose field names contain a date.

There are 24 fields whith date which gives the history of the last 2 years.

The name of the table contains the max date : table_201808

data:

Load

"Code EAN" as "Code CIP 13",

"Laboratoire (constant)",

"UN 09/2016",

"UN 10/2016",

"UN 11/2016",

"UN 12/2016",

"UN 01/2017",

"UN 02/2017",

"UN 03/2017",

"UN 04/2017",

"UN 05/2017",

"UN 06/2017",

"UN 07/2017",

"UN 08/2017",

"UN 09/2017",

"UN 10/2017",

"UN 11/2017",

"UN 12/2017",

"UN 01/2018",

"UN 02/2018",

"UN 03/2018",

"UN 04/2018",

"UN 05/2018",

"UN 06/2018",

"UN 07/2018",

"UN 08/2018",

FROM table_201808;

Thank you for your help

Johanna

Re: Load fields containing a date

MarcoWedel — Thu, 18 Oct 2018 19:45:50 GMT

Do you have a question?

Re: Load fields containing a date

Anonymous — Thu, 18 Oct 2018 20:57:34 GMT

HI Marco,

I want to automate the loading of this table whose fields date 'UN MM / YYYY' will change every month, since they correspond to the last 24 months preceding the date being on the name of the file.

Re: Load fields containing a date

MarcoWedel — Thu, 18 Oct 2018 21:06:53 GMT

please post some sample data and your expected result.

Re: Load fields containing a date

Anonymous — Sat, 20 Oct 2018 20:54:13 GMT

Hi Marco,

here an example of a table, I want to automate the loading of this table whose date fields will change every month according to the date present on the name on the file, as explained above

Re: Load fields containing a date

MarcoWedel — Sat, 20 Oct 2018 21:11:02 GMT

Hi,

maybe one solution might be:

table1:
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1561299-341498/test%20201808.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

Re: Load fields containing a date

Anonymous — Sat, 20 Oct 2018 21:40:32 GMT

Thanks for that answer indeed I did not think about it.

I have a last question.
In case I want to make a Cross table how to use the LOAD *?

Here is my script :

Directory;

CrossTable(Dates, Volumes)

LOAD [Laboratoire (constant)],

[UN 10/2016],

[UN 11/2016],

[UN 12/2016],

[UN 01/2017],

[UN 02/2017],

[UN 03/2017],

[UN 04/2017],

[UN 05/2017],

[UN 06/2017],

[UN 07/2017],

[UN 08/2017],

[UN 09/2017],

[UN 10/2017],

[UN 11/2017],

[UN 12/2017],

[UN 01/2018],

[UN 02/2018],

[UN 03/2018],

[UN 04/2018],

[UN 05/2018],

[UN 06/2018],

[UN 07/2018],

[UN 08/2018]

FROM

[..\Downloads\test 201808.xlsx]

(ooxml, embedded labels, table is Sheet1);

Thank you

Re: Load fields containing a date

MarcoWedel — Sun, 21 Oct 2018 22:27:41 GMT

just replace your fields with * like this:

table1:
CrossTable(Dates, Volumes)
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1561299-341498/test%20201808.xlsx] (ooxml, embedded labels, table is Sheet1);

Re: Load fields containing a date

Anonymous — Sun, 21 Oct 2018 23:12:36 GMT

‌Thank you Marco for your help!!