April 28, 2011, 07:10 PM
texgatorSource Transformations - Handling missing values
I am adding a new file to an exisiting flow. This file has a numeric (P15.4) field that I would be using after I have joined this file to the host file. Sometimes this new file has no data and other times the numeric field in this file may have some values filled up and some blank. Whenever the field (XYZ) is blank or has zero values, I want these values to be treated as '1'. For this, I have defined a variable in source transformation for this file as
IF XYZ EQ O THEN 1 ELSE XYZ. Also have tried IF XYZ IS MISSING THEN 1 ELSE XYZ.
I have also tried putting the MISSING=ON in the synonym definition of this field and then test it with the above statements.
However, when I use this defined field XYZ later in the 'Select Column' section, then it appears that a value of 0 is used for this. How can I make sure that whenever a value is 0 or blank for this field, it is treated as 1?
May 05, 2011, 02:46 PM
ClifFrom you use of the terms "flow" and "source transformation" I'm assuming that you are using DataMigrator.
It looks to me like you want TWO conditions in your transformation, like this:
IF XYZ EQ 0 OR XYZ IS MISSING THEN 1 ELSE XYZ;
Aslo, while it's allowed to use the same name for the transformation as an existing field, I would suggest using a different name for clarity.