multiple left join

[#1]

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

[Gysbert_Wassenaar]

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.

[#3]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[#5]

It's work.

You are really a Legend.

Thank you so much.

Regards

Vinay