Merging multiple column values in 1 column

sami · Post by **sami** » Wed Sep 15, 2004 11:27 am

Hi all,
I have a problem, I have 4 columns in the source table and I have one column in my Target..
the calculation looks something like this: I need to check the value of 1 column, and if it matches say "XXX" then I need t0 see 2nd column and if it is also "XXX" then I need to perform a calculation "Column 3 - Column4" and store that INTERGER result in my target column...
I am confused how should i do a calculation that envolves 4 input columns and 1 target column..
Can someone help...
Thanks,

KeithM · Post by **KeithM** » Wed Sep 15, 2004 11:50 am

Your calculation should not be too difficult. You can do a simple calculation like you describe in a transformation stage. The derivation for your output column will look something like this:

Code: Select all

If InputLink.Column1 = 'xxx' 
  Then If InputLink.Column2 = 'xxx' 
      Then InputLink.Column3 - InputLink.Column4
      Else 0 
   Else 0

You did not specify what your target should be if column 1 or 2 matches so you can just replace the zeros with an appropriate value. I hope this helps.

sami · Post by **sami** » Wed Sep 15, 2004 12:36 pm

Hi Keith,
Thanks a lott... I did the same thing...
I defined Stage variables and did this calculation there....
Now as I have a set of 3 keys in my table..So the same combination of the keys can not have the same value again..
So I need to write those stage variables to my target columns..So how should I do that..
I mean, when should i know that i need to update my target column???
I hope I am trying to tell what I am doing..
Thanks

KeithM · Post by **KeithM** » Wed Sep 15, 2004 12:54 pm

I'm not sure that I understand what it is that you are trying to do. Could you provide an example because I'm sure that would help?

vmcburney · Post by **vmcburney** » Wed Sep 15, 2004 8:42 pm

Consider using one stage variable that calls a routine and is set to the return value of that routine. Pass this routine all your input column values. You may find this easier then and more robust then trying to put it in derivation code. Use the "Test" button in your routine to test your program logic.

To write a stage variable to a target column either drag and drop the stage variable into the output column or type in the name of the stage variable in the derivation field.