Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
gitguto
Contributor III
Contributor III

What's the right kind of join?

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:

gitguto_0-1662167874029.png

 

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    
Labels (1)
1 Reply
acg_
Contributor II
Contributor II

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;

allancg_0-1662175890983.png

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

rgds, allan.