Find the text in 3 columns

[rob_vander]

Hi All,  I have below data set

Group:
Load *Inline [
Text, Group
abc-123-456,A
123-abc-xyz,B
456-pqr-xyz,C
abc-pqr-xyz,D
];

Data:
Load * Inline [
ID,Text1, Text2,Text3,Value
1,abc,123,def,10
2,abc,123,456,20
3,123,abc,pqr,25
4,123,abc,xyz,30
5,456,pqr,xyz,50
6,abc,pqr,xyz,60 ];

I need to search  Text from Group table into Data. For eg. abc-123-456, abc should be searched in Text1,123 should be searched in Text2 and 456 should be searched in Text3 of actual data. If all the values of Text is found ion Text1,Text2 & Text3, assign that Group to actual data. I need below output

ID,Text1, Text2,Text3,Value,Group
1,abc,123,def,10,Undefined
2,abc,123,456,20,A
3,123,abc,pqr,25,Undefined
4,123,abc,xyz,30,B
5,456,pqr,xyz,50,C
6,abc,pqr,xyz,60,D

[Qrishna · Accepted solution]

create a composite key and do Mapping/Applymap.

try below:

Group:
mapping
LOAD
Text(Text) as Key, Group
INLINE [
Text, Group
abc-123-456, A
123-abc-xyz, B
456-pqr-xyz, C
abc-pqr-xyz, D
];

Data:
Load *,
ApplyMap('Group', Key, 'Undefined') as Group;
Load Text(Text1 & '-' & Text2& '-' & Text3) as Key,* Inline [
ID,Text1, Text2,Text3,Value
1,abc,123,def,10
2,abc,123,456,20
3,123,abc,pqr,25
4,123,abc,xyz,30
5,456,pqr,xyz,50
6,abc,pqr,xyz,60 ];

exit script;

[Kushal_Chawda · Accepted solution]

@rob_vander  what @Qrishna  suggested should work. you can directly concat fields in apply map to save one load

ApplyMap('map_group', Text1 & '-' & Text2 & '-' & Text3, 'Undefined') as Group

[rob_vander]

Thanks both