topic Multiple Excel files and preceding load in QlikView

Multiple Excel files and preceding load

Anonymous — Tue, 30 Jun 2015 14:20:57 GMT

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

Re: Multiple Excel files and preceding load

alexandros17 — Tue, 30 Jun 2015 14:26:55 GMT

force concatenation writeing:

concatenate(TableName) where tablename is the preceding table name

Re: Multiple Excel files and preceding load

datanibbler — Tue, 30 Jun 2015 14:27:30 GMT

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

Re: Multiple Excel files and preceding load

Anonymous — Tue, 30 Jun 2015 14:35:40 GMT

Hi,

rename the fields or use qualify.

For example:

QUALIFY *;

Facts:

LOAD

Filed1,

Field2,

FROM...

Regards.

Re: Multiple Excel files and preceding load

Anonymous — Tue, 30 Jun 2015 14:40:12 GMT

where would I put that in the script?

Thanks

Re: Multiple Excel files and preceding load

Anonymous — Tue, 30 Jun 2015 14:59:22 GMT

Hi,

if this can help you.

Regards.

Re: Multiple Excel files and preceding load

alexandros17 — Tue, 30 Jun 2015 15:04:19 GMT

Tab1:

Load ....

Concatenate (Tab1)

Load ....

Concatenate (Tab1)

Load ....

And so on

Re: Multiple Excel files and preceding load

Anonymous — Tue, 30 Jun 2015 15:17:00 GMT

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