topic Find the string between two fields in App Development

Find the string between two fields

Nagaraju_KCS — Fri, 26 May 2023 19:41:44 GMT

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

Re: Find the string between two fields

Nagaraju_KCS — Sat, 27 May 2023 00:31:04 GMT

@marcus_sommer, @Kushal_Chawda - can you please take a look

Re: Find the string between two fields

BrunPierre — Sun, 28 May 2023 17:02:04 GMT

Hi @Nagaraju_KCS, 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;

Re: Find the string between two fields

Kushal_Chawda — Sat, 27 May 2023 21:48:31 GMT

@Nagaraju_KCS 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;

Re: Find the string between two fields

MarcoWedel — Sun, 28 May 2023 11:20:38 GMT

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');

Re: Find the string between two fields

MarcoWedel — Sun, 28 May 2023 13:22:02 GMT

or slightly shorter:

Evaluate('Pick(WildMatch('''&Description&Chr(39)&',''*'&Replace([Search String],',','*'',''*')&'*''),'''&Replace([Search String],',',Chr(39)&',''')&Chr(39)&')') as OutPut