Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have two fields - description and search fields, we have to find the value from Search String column
Example first records 100_QV_BCA_Test and if it is any word available in search string field I have to display (3rd column is my output column)
Description | Search String | OutPut |
100_QV_BCA_Test | ABC,BCA,BCC | BCA |
101_QS_BCC_Test2 | ABC,BCA,BCC | BCC |
102_QSD_CCC_Test3 | ABC,BCA,BCC | - |
Thanks in Advance
@marcus_sommer, @Kushal_Chawda - can you please take a look
Hi @qv_testing, You could do this.
Tmp:
LOAD * ,
SubField(Description,'_') as TmpD,
SubField("Search String",',') as TmpSS
Inline [
Description,Search String,
100_QV_BCA_Test,"ABC,BCA,BCC"
101_QS_BCC_Test2,"ABC,BCA,BCC"
102_QSD_CCC_Test3,"ABC,BCA,BCC"
];
MapOutput:
Mapping LOAD *
Where not IsNull(OutPut);
LOAD Description,
If(WildMatch(TmpD, '*'&TmpSS&'*'),TmpD,Null()) AS OutPut
Resident Tmp;
Data:
LOAD * ,
ApplyMap('MapOutput',Description,'-') as Output
Resident Tmp;
DROP Table Tmp;
DROP Fields TmpD, TmpSS;
EXIT SCRIPT;
@qv_testing try below
map_search_string:
mapping LOAD map_from,
'@from'&map_from&'@to' as map_to;
load Distinct trim(SubField(SearchString,',')) as map_from
FROM Table;
Data:
LOAD *,
TextBetween(MapSubString('map_search_string',Description),'@from','@to') as Output
FROM Table;
If there can only ever be one search string present in description, then this approach might work for you as well:
table1:
LOAD *,
Evaluate('Pick(WildMatch('''&Description&Chr(39)&','&Chr(39)&'*'&Replace([Search String],',','*'&Chr(39)&','&Chr(39)&'*')&'*'&Chr(39)&')'&','&Chr(39)&Replace([Search String],',',Chr(39)&','&Chr(39))&Chr(39)&')') as OutPut
Inline [
Description Search String
100_QV_BCA_Test ABC,BCA,BCC
101_QS_BCC_Test2 ABC,BCA,BCC
102_QSD_CCC_Test3 ABC,BCA,BCC
103_QSD_ABC_Test4 ABC,BCA,BCC
104_QSD_ABC_BCA_Test5 ABC,BCA,BCC
105_QSD_ABCD_Test6 ABC,BCA,BCC
] (delimiter is '\t');
or slightly shorter:
Evaluate('Pick(WildMatch('''&Description&Chr(39)&',''*'&Replace([Search String],',','*'',''*')&'*''),'''&Replace([Search String],',',Chr(39)&',''')&Chr(39)&')') as OutPut