What's the right kind of join?

[gitguto]

Hi everyone,

I need to join two tables:

TEST:

LOAD * INLINE
[

Material, orgrp, matgrp, modelo

A, AAA, 10, X
B, , 10, X
C, CCC, 20, Y
D, DDD, 30, Y

];

JOIN (TEST)
LOAD * INLINE
[

Material, orgrp

A, AAA
B, BBB
E, EEE
D, DDDC

];

The output of it is:

I dont understand why Material B is not registered in only one line. And why the second register of Material D doesnt have info in the matgrp and model fields.

My desired output would be:

MATERIAL	ORGRP	MATGRP	MODEL
A	AAA	10	X
B	BBB	10	X
C	CCC	20	Y
D	DDD	30	Y
D	DDDC	30	Y
E	EEE

[acg_]

Hello,

The join is working as it should here, so maybe a single join is not what you need.

Let me see If I understand your scenario,

Looking at you desired output:

1. Colums MATERIAL and ORGRP are key field that should never be null in the output, but only MATERIAL seems to be a key in the source data (never null);

2. Colums MATGRP and MODEL seems to be joined only to column MATERIAL, based on output.

So, from this I understand that you need to build 2 separate tables based on the source data, one to build the MATERIAL-ORGRP segment of your data and the other to build the MATERIAL-MATGRP-MODEL segment of your data, then in the end you need to join those data together having only the MATERIAL column as a key in your join.

To achieve this desired output based in the data you gave as example, I believe this should work.

NoConcatenate
Source_A:
LOAD * INLINE [
Material, orgrp, matgrp, modelo
A, AAA, 10, X
B, , 10, X
C, CCC, 20, Y
D, DDD, 30, Y
];

NoConcatenate
Source_B:
LOAD * INLINE [
Material, orgrp
A, AAA
B, BBB
E, EEE
D, DDDC
];

NoConcatenate
Aux_A:
LOAD Material, orgrp RESIDENT Source_A Where orgrp <> '' ;
JOIN(Aux_A) LOAD Material, orgrp RESIDENT Source_B Where orgrp <> '';

NoConcatenate
Aux_B:
LOAD Material, matgrp, modelo RESIDENT Source_A;

NoConcatenate
Final_Table:
LOAD * RESIDENT Aux_B;
JOIN(Final_Table) LOAD * RESIDENT Aux_A;

DROP TABLES Aux_A, Aux_B, Source_A, Source_B;

exit script;

If this does not apply to your real scenario, maybe we need more info on it.

rgds, allan.