If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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?
load * from Table 1;
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.
you can do as below:
itemNo from Table1;
Hope it helps.
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
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
// the four has not address, it is going to appear without address in the
// final result
// 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
// this one does not exist in the numbers, so in the final result is not going to appear.
// this is a duplicate, it is going to appear "twice" in the final result
// 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.
(qvd) WHERE EXISTS(numbers);