Replace a character(s) found in multiple columns of record
Posted: Thu Nov 24, 2011 12:55 pm
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.
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.