Originally posted by: pje77

I have the following mapping issue and I am looking for the best way to resolve it.

In my Input file, I have a text field which can be made of an undefined number of words separated by spaces.

I want to extract a particular word, but the only way to recognize it is to check if it is present in a list of values which is stored in another file.

example:

Input field: "FOR COMMAND NUMBER 12345 PLEASE NOTE"

List of values:
63876
89733
12345
92872
29080

I want to store "12345" in my output file.

Any help will be appreciated.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: repanzer

First you need to identify the string in the input that you want.

If in the input string "FOR COMMAND NUMBER 12345 PLEASE NOTE", you only want to check the numeric string, you can use "LEAVENUM()" and it will leave only numbers. (Just like LEAVEALPHANUM will leave only alpha and numeric values and remove spaces, etc)

If you were to know which field it was, if it were always the same format, meaning, if the string you want is always the fourth field if delimited by spaces, you can use;

WORD(in1, " ", 4).

If the string can be in more then one place, or contains alpha characters, it should be more challenging. Let me know if that is so.

One you get the string, and if the other input is defined as a text blob.
you can do a;

IF(FIND(String from input, strings in other file)>0, String From Input, NONE).

If the file is defined as OtherFile>Field(S). You can;

IF(PRESENT(EXTRACT(OtherField:File, OtherField=STring from input)), String from Input, none)

If the "other file" is sorted, you can use lookup or lookdown, which is more efficient (I believe it stops when it gets a hit, where extract will check for multiple entries in Other file, even if it can't map it, ie. only one output field. )
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: pje77

Thanks for your prompt response.

My problem is that the string can be in more then one place, or contains alpha characters!

Any help appreciated.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: repanzer

Well, as with any similar type of circumstance, you'll need a way to identify the string. If a human can't do it, then you can't code it either.

So if;

Input Card1 = input file with string (i.e. FOR COMMAND NUMBER 12345 PLEASE NOTE)

Input Card2 = reference file with possible strings;

63876
89733
12345
92872
29080

If each input1 file had one record only, and it could be:

FOR COMMAND NUMBER 12345 PLEASE NOTE

or:

FOR NUMBER 6AB76 PLEASE NOTE

In your first output card, you could do

=EITHER(EXTRACT(field:input2, FIND(field:input2, record:Input1)>0),"NOTFOUND")

EITHER says do the first thing that does not equate to NONE, so if it find a hit in the extract, it takes that string from inputcard2, else puts "NOTFOUND", so in the map rule you can say

Basically, instead of saying I have this record;

FOR COMMAND NUMBER 12345 PLEASE NOTE

And I want the string from this file
63876
89733
12345
92872
29080

That is in that record, we’re saying;

I have this file;
63876
89733
12345
92872
29080

And I want the value that is also in this string;

FOR COMMAND NUMBER 12345 PLEASE NOTE

Know what I mean?
And this is in output card 1, remember, so it will go through each field in inputcard2 and see if that string is in the input record. Then you will have that number in your outputcard1, and you can pass it to output card2, where you do your mapping.

Then when you do your full mapping in outputcard2, you say

field:ouputcard2= IF(field:outputcard1 = NOTFOUND, do what ever you want if it didn't find the hit, else, map the number. )

But that will only work if your input has one of these records and only ONE. If it has more then one, let's say 5, what will happen is it will put the string for the first inputcard1:record into outputcard1, then use that same value for each record in your file when you map each of the 5 records in outputcard2.

If it contains more then one input1 record, you would have to do the same idea in a run map, and you can, but you need a control map.

So rather then having the map that's doing the mapping as being the first map to run, you would have another map that has the same input type tree as the translation map. That map would have ONE output card with a file>Records(S), field1, field2...any number of fields you need to accomplish what you want.

Now if your inputcard1 type tree is

File>Transaction(s)RecordwithString(1:1)

And you need to map each transaction, and each transaction has ONE these record like;

FOR COMMAND NUMBER 12345 PLEASE NOTE

In your CONTROL MAP, you pass each transaction one at a time to the functional map, along with THE ENTIRE INPUTCARD 2 REFERENCE FILE, and in a field in the functional map, you do;

RUN(translation_map, ECHOIN(1, Inputcard1) ECHOIN(2, InputCard2).

ECHOIN says when you run this map, override input card 1 with the text I supply, in this case, that would be ONE transaction from input file 1, and over ride input card 2 with the entire reference file, because you passed the entire reference file to your functional map.

Overall, what will happen is;

A file is dropped with 5 transactions and kicks off the control map. The control map validates the data, then takes the first transaction and entire inputcard 2, and goes into the functional map. In the functional map, you do the RUN statement to run the translation map, overriding the input with the first transaction. It builds output card 1 and gets the number we’re looking for. The translation map then goes to output card 2, passing the value from output card 1, and you map. It finishes, control goes back to the control map. The control map grabs the second transaction, goes in the functional map, runs the translation with the second transaction. It put the number in output card 1, then passes that number to outputcard2 where you do the mapping. Being you set the second output card in the translation map to Append, it append the info to the file, releases control to the control, which goes through the entire file.

While someone may offer a more simple way to do this, and I know this may sound complex, it's really not, and most importantly, following this example will give you an idea of how to achieve more complex mapping situations in WTX, and the control map with RUN statement concept is essential in doing so.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange