Originally posted by: SystemAdmin

Hello! Have a transform map with one input and one output card currently.

Need to match on SSN to a table and pull off the first and last name and the marital status, and place these in three distinct output card fields.

Have done table lookups previously both in the same map and also from a Run Map call. However, those only had to retrieve one field on a match. Trying to avoid having three different lookups, one for each rule on the output card for the necessary fields.

Not sure how to retrieve the table data and parse it into three different output fields and also how and where to put the one lookup or Run Map call in the current map.

Would need another input card for the table if do a lookup but if a Run Map is used then that table input can be placed in that map.

Would appreciate any help or examples regarding this map.

Thanks!
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

If your current output card is a "work card" (not directly output through an adapter because you are using the "sink" adapter or the file adapter with the !Create option) you can add a new text element to the top of the card.

If your current output card is not a work card. Add a new card and make it output card 1. The type tree just needs to have a single text field element.

Put your DBLOOKUP with "select *" to return all rows or "select Row1 Row8 ..." to get specific rows. Wrap the DBLOOKUP with the VALID function so you can decide what you want to do if the function fails because the DB is down. I use VALID(DBLOOKUP(...),FAIL(DB failure blah blah)). You can further wrap that with

EITHER(VALID(DBLOOKUP....),FAIL(...)),Default value or action if not found)

The element will receive the database colums as DATA1 | DATA2 | ... in the order you specified them emplicitly or the order of the columns in the table if you used the "select *".

Use the WORD function with the column separator to get the data element for the column you are after in each of your target elements. For example:

=WORD (DBdata, "|", 2)

Adjust this rule as appropriate if you used the EITHER function above and put a default value or error flag value in the element.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Hello rreeves! Thanks for the quick reply today.

Have to say sorry but made a mistake here. Failed to mention that the table is not a database table, just a text table. However, using your same concept the WORD function should be able to parse out the data needed for each output field from the LOOKUP rule in the first field.

Would follow your second example where the current output card is not a work card as it writes with the File adapter directly to the output file. However, if a work card is a better way using a text table then will definitely try that.

For your example with the WORD function, this is assuming there is a delimeter between each field but for this example the delimiter would be a space or spaces between the last name, first name and marital status fields.

Please indicate if this same concept is fine for a text table lookup or if something else needs to be added here. Very sorry about the original explanation but will definitely be able to use that for future lookups to database tables.

Thanks!
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

If I understand correctly you have something like:

Input File:
Record(s) SSN, field2, field3....
Lookup File:
Record(s) SSN, FirstName, LastName, MaritalStatus, ...
Output File:
Record(s) SSN, FirstName, LastName, MaritalStatus ...

For each input file record you want to match to the Lookup File using the SSN and get the Name and marital status.

It would be best if your lookup file had the first and last names as individual "columns" with some kind of separator so you could define the individual name elements in your tree. If your lookup file has just a single freeform text field that the name is in you could have some issues if the names do not always appear the same way. (If this is the case you could look at using Ascential's QualityStage product to first create an output with standardized names for you.)

A couple of other design decisions have to do with the size and order of the input and lookup files....

If the input file was very large and had many rows with the same SSN and was sorted, I might change the tree to create a control break on the SSN so I only did the lookup once per SSN.

If the lookup file is very large, I would think about either putting it in a table or sorting it so I could use the SEARCHUP function instead of the LOOKUP.

Let's say the files are not too big and performance isn't a big issue:

On the output card for the Record(s) group define a functional map that looks something like this:

=F_EachRec(Record:Input, LOOKUP(Record:Lookup, SSN:Record:Input=SSN:Record:Lookup)

In the functional map you would see two input cards, one with a row of input and one with a row of the lookup file and you map to the one row of output.

If this doesn't match what you are looking for I'll have to get my crystal ball serviced.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Hello! Thanks to both of you for your replies last week.

Have been taking the SEARCHUP functional map approach here. The new output card was added which has one text field which contains all the data retrieved from the SEARCHUP which is Last Name, First Name and the Marital Status fields, =TEXT(SEARCHUP(Row_Fields_To_Retrieve:Row:BO_Hewitt_SPPARTT_Table_In, SOCIAL_SECURITY_NU Column:Row:BO_Hewitt_SPPARTT_Table_In, HEW_DET_BEN_RECI_SSN Field:HEW_DETAIL_RECORD Record:BO_Hewitt_Original_Rec_Input)), the preceding is the rule for the text field.

This text item, called SPPARTT_TABLE_SRCH_OUTPUT, is sent with the functional map rule on the second output card, =F_Map_to_BO_Hewitt_Conv_New_Out(HEW_DETAIL_RECORD Record:BO_Hewitt_Original_Rec_Input, SPPARTT_TABLE_SRCH_OUTPUT) which is fine.

The functional map builds 10 output fields of which 3 are the Last, First and Marital Status fields, these 3 are built as follows

Last - =LEFT(Input_From_Tbl_Srch_Output, 25)
First - =MID(Input_From_Tbl_Srch_Output, 31, 15)
Marital Status - =RIGHT(Input_From_Tbl_Srch_Output, 1)

Had some First Names as blanks so used the string functions to parse the data instead of the WORD function.

This all works great and the output records look fine, correct length and everything, however, the Last, First and Marital Status information is the same for all the output records produced. The information in these fields is from the first matched SSN from the input file to the table, it is apparently not going to the next input record after the first match is found and the output record is built in the functional map.

Must have the functional map call, which is on output card 2 in the primary map, in an incorrect place or it is not set up correctly so that the next matched SSN and its table output is fed to the functional map.

Can you think of what might be causing this, maybe an incorrect range on the table search output item in the tree, guess it is 1:1 since the map just pulled this type from the tree but maybe it needs to be in a Group with a 1:s range component.

Will test a few things but not really sure why this is happening.

Would appreciate any more help that you can offer here.

Thanks!
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Hello! Have resolved the issue in the last post and it is working now.

The issue was that the map had your two recommendations combined into one concept but just took your latest recommendation and used the SEARCHUP within the functional map call. This reads the next input record and matches the SSN and the output card is just a matter of dragging the table output sent to the functional map to the output fields.

The point about adding an extra output card to the primary map was not needed as that was recommended in your first approach so that card was deleted from the map.

However, will keep that concept for future reference if something like that is needed.

Very much appreciate all your help and pointing this developer in the right direction with a very workable and easy solution to code.

Thanks!
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender