Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

adrianbuzer
Contributor II

Multiple Excel files and preceding load


Hi

I am trying to load multiple Excel files which are all in the same format with the same field names and number of fields.  I am calculating new fields and also calculating new fields from the calculated fields using a preceding load.  The result is that I have 1 table with the 3 extra fields calculated in the preceding load, 1 table with just the original field names and a synthetic key table as follows

Table 1

$Syn 1

     field1         

     field2

     field3

     field4

calcfield1

calcfield2

calcfield3

Table 2

$Syn 1

     field1

     field2

     field3

     field4

$Syn 1 Table

field1

field2

field3

field4

How can I prevent the creation of the synthetic key?

Thanks

1 Solution

Accepted Solutions
datanibbler
Esteemed Contributor

Re: Multiple Excel files and preceding load

Hi,

do you have that PRECEDING LOAD before you have loaded all the original files? that may be the reason - generally, when you have such a large synthetic key, it means that for some reason your tables were not concatenated correctly, but you ended up with two tables which QlikView regards as separate, but which share all but a few fields (names are equal, thus the synthetic key, QlikView links those automatically).

Try to find out why the tables were not properly appended to one another.

I would recommend you to try placing your PRECEDING LOAD at the very end, after the original files have all been loaded and appended, or making it a RESIDENT LOAD to begin with.

HTH

7 Replies

Re: Multiple Excel files and preceding load

force concatenation writeing:

concatenate(TableName) where tablename is the preceding table name

datanibbler
Esteemed Contributor

Re: Multiple Excel files and preceding load

Hi,

do you have that PRECEDING LOAD before you have loaded all the original files? that may be the reason - generally, when you have such a large synthetic key, it means that for some reason your tables were not concatenated correctly, but you ended up with two tables which QlikView regards as separate, but which share all but a few fields (names are equal, thus the synthetic key, QlikView links those automatically).

Try to find out why the tables were not properly appended to one another.

I would recommend you to try placing your PRECEDING LOAD at the very end, after the original files have all been loaded and appended, or making it a RESIDENT LOAD to begin with.

HTH

ggallina
Contributor III

Re: Multiple Excel files and preceding load

Hi,

rename the fields or use qualify.

For example:

QUALIFY *;

Facts:

LOAD

         Filed1,

         Field2,

FROM...

Regards.

adrianbuzer
Contributor II

Re: Multiple Excel files and preceding load

where would I put that in the script?

Thanks

ggallina
Contributor III

Re: Multiple Excel files and preceding load

Hi,

if this can help you.

Regards.

Re: Multiple Excel files and preceding load

Tab1:

Load ....

Concatenate (Tab1)

Load ....

Concatenate (Tab1)

Load ....

And so on

Highlighted
adrianbuzer
Contributor II

Re: Multiple Excel files and preceding load

I loaded the basic table first from all the spreadsheets as a temp table then did the preceding load using a resident load on the temp table

Thanks

Community Browser