topic Re: Multiple file load wither inner join in QlikView

Multiple file load wither inner join

Thu, 09 Jul 2015 10:32:14 GMT

Hello,

I have tried to load from multiple files 3 tables, which I wanted to concatenate. So I've used the Load From [Path\*.xlsx] (all my files ae xlsx and in the same folder).

Rapport1:

LOAD *

FROM [pahtfield*.xlsx](ooxml, embedded labels, table is [Rapport 1]);

inner join (Rapport1)

LOAD *

FROM

[pathfield\*.xlsx]

(ooxml, embedded labels, table is [Rapport 2]);

But I had no data loaded

Any one can help my with this?

Thank you

Re: Multiple file load wither inner join

Thu, 09 Jul 2015 10:39:44 GMT

May be both the files doesn't have common data. You are using Inner Join.

Regards,

KKR

Re: Multiple file load wither inner join

Anonymous — Thu, 09 Jul 2015 10:44:20 GMT

When you use inner join, the resulting table contains the records that have common values in their key fields. Since you're using *, we cannot see if there are key fields at all. And, if there are - they don't have matching values.

Re: Multiple file load wither inner join

swuehl — Thu, 09 Jul 2015 10:44:34 GMT

First load your two tables from your sources, then do the inner join in a resident load. Then drop the temporary second table:

RAP1:

LOAD * FROM ...;

RAP2:

LOAD * FROM ...;

INNER JOIN (RAP1) LOAD * RESIDENT RAP2;

DROP TABLE RAP2;

Re: Multiple file load wither inner join

Thu, 09 Jul 2015 10:47:19 GMT

No, I am sure the have common date, when I used only one file it worked, but when i used the path\*.xlsx it didn't

Re: Multiple file load wither inner join

maxgro — Thu, 09 Jul 2015 10:51:54 GMT

you said concatenate

wanted to concatenate

and in the script you used join

do you want to concatenate or join?

maybe this can help to choose

Re: Uses for Left Join and Concatenate in tables?

Re: Multiple file load wither inner join

swuehl — Thu, 09 Jul 2015 10:52:40 GMT

That's because the JOIN will be performed after each file has been loaded, not after concatenating all files.

If the Key values are exclusive in the files, you'll end up with a empty set.

Re: Multiple file load wither inner join

Thu, 09 Jul 2015 11:03:01 GMT

that is exactly what happening to me. I want to first concatenate the sheets from the files and then join them

Re: Multiple file load wither inner join

swuehl — Thu, 09 Jul 2015 11:06:26 GMT

Have you tried what I suggested above (First LOAD the two tables, then JOIN the resident tables)?

Re: Multiple file load wither inner join

Thu, 09 Jul 2015 11:17:01 GMT

I don't know how the script two merge to resident tables, can u give me a sample of script please?

Re: Multiple file load wither inner join

swuehl — Thu, 09 Jul 2015 11:26:55 GMT

I thought I've done that already with my first post...

//Assuming that all tables [Rapport 1] have same structure, so LOAD will autoconcatenate

//Assuming that all tables [Rapport 2] have same structure, so second LOAD will autoconcatenate

//Assuming that Rapport1 and Rapport2 have some, but not all common fields, and some matching key field values

Rapport1:

LOAD *

FROM [pahtfield*.xlsx](ooxml, embedded labels, table is [Rapport 1]);

Rapport2:

LOAD *

FROM

[pathfield\*.xlsx]

(ooxml, embedded labels, table is [Rapport 2]);

INNER JOIN (Rapport1) LOAD * RESIDENT Rapport2;

DROP TABLE Rapport2;