topic Re: Table names not changed in load script in QlikView

Table names not changed in load script

Thu, 19 Mar 2015 13:23:42 GMT

Hi, I'm trying to load different Excel sheets in a script to different tables but altrhoug I give a table name before each load statement, all sheets are loaded to the same table:

In the below example, the 2014 data is loaded in the FeesSA2015 table instead of in the FeesSA2014 table.

Any ideas?

=====

Directory;
FeesSA2015:
LOAD.

...

FROM
[..\T_FEES_ARCOLE 2015.xlsx]
(ooxml, embedded labels, table is T_FEES_ARCOLE);

Directory;
FeesSA2014:
LOAD.

...

FROM
[..\T_FEES_ARCOLE 2014.xlsx]
(ooxml, embedded labels, table is T_FEES_ARCOLE);

Re: Table names not changed in load script

Roop — Thu, 19 Mar 2015 15:22:42 GMT

I think that it is because the data fields are identical - so QlikView loads the data into a single table.

I tried a similar set of circumstances and got the same results. If you wish different tables use different names like this:

ABC:

LOAD EXCHANGECODE,

FROMDATE,

TODATE,

EXCHANGERATE

FROM

(ooxml, embedded labels, table is Sheet3);

DEF:

LOAD EXCHANGECODE as EE,

FROMDATE as DD,

TODATE as CC,

EXCHANGERATE as BB

FROM

(ooxml, embedded labels, table is Sheet4);

But I would suggest that you want to load the data into a single table and flag it differently. So I would change your script to read something like:

Directory;
Fees:
LOAD

2015 as Year,

...

FROM
[..\T_FEES_ARCOLE 2015.xlsx]
(ooxml, embedded labels, table is T_FEES_ARCOLE);

concatenate LOAD

2014 as Year,

...

FROM
[..\T_FEES_ARCOLE 2014.xlsx]
(ooxml, embedded labels, table is T_FEES_ARCOLE);

Re: Table names not changed in load script

stigchel — Thu, 19 Mar 2015 15:30:09 GMT

I agree with Rupert about loading one table, should you still want otherwise you can also use the NoConacatenate statement before each load statement after loading the first table.

And use Qualify *;, which will instead of manually having to rename the fields will place the table name in front of the Field name.

Re: Table names not changed in load script

maxgro — Thu, 19 Mar 2015 15:59:15 GMT

if you add a noconcatenate you'll get two tables (and a syn Table)

Directory;
FeesSA2015:
LOAD. ...

FROM
[..\T_FEES_ARCOLE 2015.xlsx]
(ooxml, embedded labels, table is T_FEES_ARCOLE);

FeesSA2014:

noconcatenate
LOAD. ...

FROM
[..\T_FEES_ARCOLE 2014.xlsx]
(ooxml, embedded labels, table is T_FEES_ARCOLE);

maybe you can concatenate and add a field to "remember" the source

Directory;
FeesSA:
LOAD. ..., 2015 as source

FROM
[..\T_FEES_ARCOLE 2015.xlsx]
(ooxml, embedded labels, table is T_FEES_ARCOLE);

concatenate (FeesSA)
LOAD. ..., 2014 as source

FROM
[..\T_FEES_ARCOLE 2014.xlsx]
(ooxml, embedded labels, table is T_FEES_ARCOLE);