Replace a character(s) found in multiple columns of record

[darrreever]

Hello:

Question: What is the best way to replace a character(s) that is found in multiple columns of a record with a standard value?

Condition: Picture a record with 10 fixed length columns. Five of the columns contain a character in the last position that needs to be converted to a standard value. The incoming characters (11 distinct values) map to a corresponding standard value, e.g. a=5, b=6, etc. Normally I could use a stage variable or a lookup table to read / look-up the input column and calculate/return a value. However, using stage variables I would need 5 different stage variables, corresponding to the five columns, each containing the mapping logic; using a lookup stage, I would need to use 5 look-up stages because one input column can only map to the key value of the look-up table. Further complicating this scenario is the fact that the original record is part of a file that contains multiple "record" types and each of these record types has the same requirement to replace the value found in multiple columns of the record.

Potential Solutions (to prime the creative solution pump or to help others in a similar situation): 1) Develop a single job reading the flat file with multiple records and using multiple look-ups on each record type "link" would be "busy". 2) Develop multiple jobs reading a specific record type in each job would be slightly cleaner. 3) Use a pivot stage to put all of the columns in a single "value" column (with necessary ID columns to map back to the original record) but this technique seems a little "rube goldberg". 4) Put the record in a table and writing a stored procedure to perform the mapping. or 5) Write a unix script to map the values in the original file.

Thanks in advance for your help.

[ray.wurlod]

If all the standard values are single character, you can use a Convert() function on the final character. For example:

Code: Select all

Left(InLink.TheString,Len(InLink.TheString)-1) : Convert("abcdefghijk", "zyxwvutsrqp", Right(InLink.TheString,1))

[darrreever]

Hello DataStagers!

Thank you for the insight Ray. You are always on top of things!

This is what I did. Since the record has multiple ooccurrences of the value I needed to replace and some of those occurrences did not need to be replaced, I just 1) used the Pivot Stage to pivot the columns that had the values needing to be replaced, 2) substringed the column to get the value to be replaced, 3) use a look-up stage to a flat file that had a value pair listing of the values and lastly, 4) concatenated that "found value" in its proper position in the field.

Thanks again for all of your help.

God Bless!