Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.