Dynamic String Parsing

Situation:

I have a requirement to parse strings from an particular field from an input file.
The input field can have from 1 to 100 or more values delimited by a semicolon.
All the records in the input file will have this field. But each record can have different number of values for this field.
For e.g. record one can have this field with value '1123;1214;1151;'
and record two can have '12431'
and record three can have '123';'1241'; can have 100 or more values separated by semicolon etc
and record four can have '1241;4563'
Once each value is parsed, this value will be used to extract a value from a look up table using vlookup (DBquery select) function and the output field will have multiple emails separated by semicolons.
e.g.
Lookup table
id email
1123 abc@ab.com
12431 qw@abc.com
1214 abcd@xy.com
1151 bcd@yz.com
So for record 1 that field need to have below value. This value with mutliple emails need to be passed to the output file.
record 1 'abc@ab.com;abcd@xy.com;bcd@yz.com'
record2 'qw@abc.com'


Solution:

This can be achieved by using "Tokenize" function and below are the steps to achieve this requirement:

 

1. Create two Local variable at Root level of the Target Schema.

*In one variable(say varQuote) save '.

* In Second variable (say varID) use Translate function for handing the Quotes in the field values. Syntax for varID:
translate( $Input_Source_Schema/Root/Record/empid , $varQuote , '' )

2. Create a Local variable for passing the current value and map it(Say varVal). Below is the variable Value:
string(.)

3. Use the below Tokenize function inside ForEach at Record level of the Target Schema. Tokenize syntax:
str:tokenize($varID, ';')

4. Use the DB Lookup to pass the Email id corresponding to the ID in the field in which you want the emailID to be sent.

DBQuery {select email from info1 where id = '$varVal' ,$conninfo ,false}

 

Have more questions? Submit a request

0 Comments

Article is closed for comments.