Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
adrianfer
Contributor III
Contributor III

Help on data model

Given these tables:

 

TableA:
load * inline [
Code, Level
Shirt, Level A
Shorts, Level A
Skirt, Level A
Pen, Level A
Skirt, Level B
];

Relation:
load * inline [
Code, Related
Shirt, Shorts
Shirt, Skirt
];

The desired output is:

Code Level Match
Pen Level A  
Shirt Level A Related
Shorts Level A Related
Skirt Level A Related
Skirt Level B  

 

That per Level:

  • if the Code and any of the Related from Relation table are in TableA

Then Match = Related

Labels (1)
5 Replies
MayilVahanan

Hi

How come Level B - Skirt as not related?

Try like below

Relation:
load * inline [
Code, Related
Shirt, Shorts
Shirt, Skirt
];

Concatenate
Load Distinct Code, Code as Related Resident Relation;

MapRelated:
Mapping Load Related, 'Related' Resident Relation;

TableA:
load *, ApplyMap('MapRelated', Code, '') as Match inline [
Code, Level
Shirt, Level A
Shorts, Level A
Skirt, Level A
Pen, Level A
Skirt, Level B
];

Drop table Relation;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
vinieme12
Champion III
Champion III

or Use Exists()

 

Relation:
load Code as code_relation, Related as rel_relation inline [
Code, Related
Shirt, Shorts
Shirt, Skirt
];

TableA:
load *,if(Exists(code_relation,Code) or Exists(rel_relation,Code),'Match','') as Match inline [
Code, Level
Shirt, Level A
Shorts, Level A
Skirt, Level A
Pen, Level A
Skirt, Level B
];
drop table Relation;

exit Script;

 

vinieme12_0-1690907544880.png

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

Alternatively merge codes that need to be compared in a single column using crosstable()

 

CrossTable(type, codes, 1)
Relation:
load recno() as RowKey, * inline [
Code, Related
Shirt, Shorts
Shirt, Skirt
];

TableA:
load *,if(Exists(codes,Code) ,'Match','') as Match inline [
Code, Level
Shirt, Level A
Shorts, Level A
Skirt, Level A
Pen, Level A
Skirt, Level B
];
drop table Relation;

exit Script;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
adrianfer
Contributor III
Contributor III
Author

 Level B - Skirt  is not related because of the rule:

That per Level:

  • if the Code AND any of the Related from Relation table are in TableA

Then Match = Related

Since Skirt is the only one in Level B, then it is not Related. 

adrianfer
Contributor III
Contributor III
Author

Maybe this is a better example of the problem:

TableA:
load * inline [
Code, Level, Nike, Adidas, Fila
Shirt, Level A,1,0,0
Shorts, Level A,1,0,0
Skirt, Level A,0,1,0
Pen, Level A,0,0,1
Skirt, Level B,1,1,0
];

Relation:
load * inline [
Code, Related Code
Shirt, Shorts
Shirt, Skirt
];

The desired output is:

Row Code Level Nike  Adidas Fila Result
1 Pen Level A 1      
2 Shirt Level A 1     Match
3 Shorts Level A   1   Match
4 Skirt Level A     1 Match
5 Skirt Level B 1 1   Match

 

Business rule for column Result: Will have a value of Match if, in one Level, a Code has both Nike and Adidas equal to 1.

Rows 2,3,4 will sort of be considered as one Row, as per the Relation table. Both 3 and 4 are Related Codes to 2. Hence for these three Rows, the Result is Match. Row 2 is Nike and Row 3 is Adidas.