Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Nov. 30th, Webinar! Do More With Qlik Cloud Catalog & Lineage: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bazzaonline
Creator
Creator

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
Specialist
Specialist

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
Creator
Creator
Author

So I literally type concatinate after the first table load?

surendraj
Specialist
Specialist

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
Specialist
Specialist

Yes.

Table 1 structure is different from all 4 tables right?

agigliotti
Partner - Master III
Partner - Master III

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
Champion
Champion

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
Creator III
Creator III

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