Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
bazzaonline
Contributor

Help with Qlik Sense load Script

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

7 Replies
surendraj
Valued Contributor

Re: Help with Qlik Sense load Script

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
Contributor

Re: Help with Qlik Sense load Script

So I literally type concatinate after the first table load?

surendraj
Valued Contributor

Re: Help with Qlik Sense load Script

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
Valued Contributor

Re: Help with Qlik Sense load Script

Yes.

Table 1 structure is different from all 4 tables right?

agigliotti
Honored Contributor II

Re: Help with Qlik Sense load Script

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
Esteemed Contributor

Re: Help with Qlik Sense load Script

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
Contributor II

Re: Help with Qlik Sense load Script

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);