Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
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.