topic Re: multiple left join in QlikView

multiple left join

Fri, 28 Nov 2014 15:03:26 GMT

Dear all,

I have a one table like below

Part_ID	Catalogue_ID
1	101
2	102
3	103
4	104
5	105
6	106
7	107
8	108
9	109
10	110

other table like

Part_ID	Catalogue_ID	Qty
1	101	30
	102	40
3	103	50
	104	65
5		20
8		52
9	109	45
10		63

and I want something like below in staight table...

Part_ID	Catalogue_ID	Sum(Qty)
1	101	30
2	102	40
3	103	50
4	104	65
5	105	20
6	106	0
7	107	0
8	108	52
9	109	45
10	110	63

i have a script like

master:

LOAD Part, Catalogue_ID
FROM
C:\temp\BookQ1.xlsx
(ooxml, embedded labels, table is Sheet1);

left join (master)
LOAD Part,
//Catalogue_ID,
Qty
FROM
C:\temp\BookQ1.xlsx
(ooxml, embedded labels, table is Sheet2);

left join(master)
LOAD
//Part,
Catalogue_ID, Qty
FROM
C:\temp\BookQ1.xlsx
(ooxml, embedded labels, table is Sheet2);

but its not give me desire result.

Please help.

Vinay

Re: multiple left join

Gysbert_Wassenaar — Fri, 28 Nov 2014 15:19:27 GMT

Try this:

Temp:

LOAD * INLINE [

Part_ID, Catalogue_ID, Qty

1, 101, 30

,102, 40

3, 103, 50

,104, 65

5, , 20

8, , 52

9, 109, 45

10, , 63

];

mapPC:

mapping LOAD Part_ID as K, Qty as V Resident Temp;

mapping LOAD Catalogue_ID as K, Qty as V Resident Temp;

Result:

NoConcatenate

LOAD *, ApplyMap('mapPC',Part_ID, ApplyMap('mapPC',Catalogue_ID,0)) as Qty INLINE [

Part_ID, Catalogue_ID

1, 101

2, 102

3, 103

4, 104

5, 105

6, 106

7, 107

8, 108

9, 109

10, 110

];

drop table Temp;

You can replace the inline loads with load statements for your real data sources. The above is just an example.

Re: multiple left join

Fri, 28 Nov 2014 15:52:34 GMT

Dear Gysbert,

Part_ID 6 & 7 missing.

It does not display on Straight table even if check on "Show All values"

FYI: It display on Table Box but I want in Straight Table

Please help

Regards

Vinay

Re: multiple left join

Gysbert_Wassenaar — Fri, 28 Nov 2014 15:58:19 GMT

Part_ID 6 & 7 missing.

It does not display on Straight table even if check on "Show All values"

Disable the Suppress Zero-Values option on the Presentation tab.

Re: multiple left join

Fri, 28 Nov 2014 16:03:35 GMT

It's work.

You are really a Legend.

Thank you so much.

Regards

Vinay