Dynamic string creation

[micheledenardi]

Dear All,

i have a string with mutiple values, separated by a pipe, like this:

Value1|Value2|Value3

and i would like to create a script to dynamically replace 1 Value and create all combinations of Value1,Value2 and Value3 and a fixed string, for example ANY in order to get this result:

Value1|ANY|ANY
Value1|Value2|ANY
Value1|ANY|Value3
ANY|Value2|ANY
ANY|Value2|Value3
....
and all other possible combinations

the order of the fields must remain always the same, so Value1 as first, Value2 as second, Value3 as third..

Any ideas?

[Kushal_Chawda]

@micheledenardi  where this String is stored? Is Value1, Value2 & Value3 are individual field values or static?

[micheledenardi]

This string is composed by fields name which contains values

[Kushal_Chawda · Accepted solution]

@micheledenardi  try below

Let's say I have below data. Field 1, Field 2 & Field 3 are the fields combined to create String

Data:
LOAD * Inline [
ID,Field 1,Field 2,Field 3
1,a,aa,1a
2,b,bb,ba
];

Now you can use below code to generate combination dynamically using 3 fields

temp:
Load * Inline [
FieldsRequire
Fields
ANY ];

// Your string is combination of 3 fields so 3 join is required to generate all combination
combinations:
Load FieldsRequire as FieldsRequire1_temp
Resident temp;

Join
Load FieldsRequire as FieldsRequire2_temp
Resident temp;

Join
Load FieldsRequire as FieldsRequire3_temp
Resident temp;

Drop Table temp;

// Replace [Field 1], [Field 2] & [Field 3] with your actual field names
final_combination:
NoConcatenate
Load if(FieldsRequire1_temp='Fields','[Field 1]',chr(39) & FieldsRequire1_temp & chr(39)) as FieldsRequire1,
     if(FieldsRequire2_temp='Fields','[Field 2]',chr(39) & FieldsRequire2_temp & chr(39)) as FieldsRequire2,
     if(FieldsRequire3_temp='Fields','[Field 3]',chr(39) & FieldsRequire3_temp & chr(39)) as FieldsRequire3
Resident combinations;

Drop Table combinations;

Now you can generate all possible combination and concatenate them within a loop

for i=0 to NoOfRows('final_combination')-1

let vField1 = Peek('FieldsRequire1',$(i),'final_combination');
let vField2 = Peek('FieldsRequire2',$(i),'final_combination');
let vField3 = Peek('FieldsRequire3',$(i),'final_combination');

Possiblecombination:
Load Distinct $(vField1) & '|' & $(vField2) & '|' & $(vField3) as Key_Comination
Resident Data;

Next i

drop table final_combination;