topic Re: joining multiple tables in QlikView

joining multiple tables

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I am new to qlik and over all coding, I complied data in 2 sheets in excel that look like below. sheet one shows as

sheet 1

WareHouse	PartNum	Qty	Price	Loc	Wrrnty
a			25	1
a				1	y
b		1			n
c	5496		10
e	6319	7	2	7

sheet 2

WareHouse	PartNum	Qty	Price	Loc	Wrrnty
a	1868	3			y
a	2121	8	7
b	1296		150	2
c		10		3	y
e					n

I am trying to bring the information missing from sheet 2 into sheet one to

, this is what its creating which is technically correct, but I want it to sheet to have the according fields individually

WareHouse	Loc	PartNum	Price	Qty	Wrrnty
a	1	-	25	-	-
a	1	-	-	-	y
a	-	1868	-	3	y
a	-	2121	7	8	-
b	2	1296	150	-	-
b	-	-	-	1	n
c	3	-	-	10	y
c	-	5496	10	-	-
e	7	6319	2	7	-
e	-	-	-	-	n

What is the solution to this ? I hope my question makes sense.

Please help and thanks in advance.

Re: joining multiple tables

Nicole-Smith — Fri, 23 Feb 2018 22:30:33 GMT

Next time, you may want to include what you WANT the output to be. It's hard to tell from your description exactly what you're looking for. I'm going to give it a shot anyway.

The following load script:

Data:
LOAD WareHouse AS WareHouse1, 
     PartNum AS PartNum1, 
     Qty AS Qty1, 
     Price AS Price1, 
     Loc AS Loc1, 
     Wrrnty AS Wrrnty1,
     RowNo() AS Row
FROM [example_data.xlsx] (ooxml, embedded labels, table is [sheet 1]);


LEFT JOIN (Data)
LOAD WareHouse AS WareHouse2, 
     PartNum AS PartNum2, 
     Qty AS Qty2, 
     Price AS Price2, 
     Loc AS Loc2, 
     Wrrnty AS Wrrnty2,
     RowNo() AS Row
FROM [example_data.xlsx] (ooxml, embedded labels, table is [sheet 2]);


LEFT JOIN (Data)
LOAD Row,
 IF(LEN(WareHouse1)>0, WareHouse1, WareHouse2) AS WareHouse,
 IF(LEN(PartNum1)>0, PartNum1, PartNum2) AS PartNum,
 IF(LEN(Qty1)>0, Qty1, Qty2) AS Qty,
 IF(LEN(Price1)>0, Price1, Price2) AS Price,
 IF(LEN(Loc1)>0, Loc1, Loc2) AS Loc,
 IF(LEN(Wrrnty1)>0, Wrrnty1, Wrrnty2) AS Wrrnty
RESIDENT Data;


DROP FIELDS WareHouse1, WareHouse2, PartNum1, PartNum2, Qty1, Qty2, Price1, Price2, Loc1, Loc2, Wrrnty1, Wrrnty2, Row FROM Data;

Will return a data table that looks like this:

WareHouse	PartNum	Qty	Price	Loc	Wrrnty
a	1868	3	25	1	y
a	2121	8	7	1	y
b	1296	1	150	2	n
c	5496	10	10	3	y
e	6319	7	2	7	n

If this is not what you're looking for, please provide the output that you want.

Re: joining multiple tables

Anonymous — Fri, 23 Feb 2018 22:42:41 GMT

Yes Nicole, that is exactly how I was trying to get it to compile for future job issues. I was trying to get the missing information to cross from each table and reflect on 1 as you have.

Thank you so much for the srcipt, there would be multiple ways to do the script, I guess it would be with what the person is familiar with.

Thank you so much!!!