Help with Qlik Sense load Script

[bazzaonline]

Hello,

I have 5 files,

1. List of Item Numbers

2. List of Item Numbers and address

3. List of Item Numbers and address

4 List of Item Numbers and address

5. List of Item Numbers and address

What I need to do is create a table in the load script that contains all the item numbers from file 1, then joins the addresses from the other 4 files only when the item number appears in the first file.

Can anyone help?

Thanks Paul

[surendraj]

Load first table

concatinate

load all 4 tables(it will auto concatinate if number of fields and names are same)

where exits( item numbers);

[bazzaonline]

So I literally type concatinate after the first table load?

[surendraj]

To simplify!!

load * from Table 1;

concatinate

Load * from Table2;

Load * from Table3;

Load * from Table4;

Load * from Table5;

where exits( item numbers);

Here all 4 tables are auto-concatinated and Resulted into one single table.

[surendraj]

Yes.

Table 1 structure is different from all 4 tables right?

[agigliotti]

you can do as below:

load

itemNo from Table1;

left join

load

itemNo,

address1

from Table2;

left join

load

itemNo,

address2

from Table3;

left join

load

itemNo,

address3

from Table4;

left join

load

itemNo,

address4

from Table5;

Hope it helps.

[YoussefBelloum]

I advise you this:

1. use variable to detect the file number from the file name

2. load dynamically file 2 to 4 with a loop using the variable

3.right join load the first table

[simotrab]

Hi, I've tried to create some fake data, to see if the result is going to be as you'd like to have. Probably my solution is the longest, and there are also some cases to see.

Hope it helps!

// load the first table

numbers:

// the four has not address, it is going to appear without address in the

// final result

load*Inline

[numbers

1

2

3

4

5

];

// store it in one folder and free some space

Store numbers Into [lib://store/numbers.qvd](qvd);

drop table numbers;

// load all the addresses in one table

address:

// this one does not exist in the numbers, so in the final result is not going to appear.

load*Inline

[numbers,address

6,f

]

;

concatenate

load*Inline

[numbers,address

1,a

]

;

concatenate

load*Inline

[numbers,address

5,e

]

;

concatenate

load*Inline

// this is a duplicate, it is going to appear "twice" in the final result

[numbers,address

1,a

]

;

concatenate

load*Inline

[numbers,address

2,b

3,c

]

;

// store the addresses and free some memory

Store address Into [lib://store/address.qvd](qvd);

drop table address;

// load the stored files, and right join them, having only the addresses whom

// number exists in the number table.

LOAD

    numbers

FROM [lib://store/numbers.qvd]

(qvd);

JOIN

LOAD

    numbers,

    address

FROM [lib://store/address.qvd]

(qvd) WHERE EXISTS(numbers);