Help on data model

[adrianfer]

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

[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;

[vinieme12]

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]

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;

[adrianfer]

 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]

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.