Logic in transformer required

[krisp321]

Hi
I have a requirement like this

Code: Select all

Col1 Col2 Col3
A     -     X
A    -1     X
A    -3     X
A     2       
A     5     X
A     0     X

If the value in Col2 <=0 then the Col3 = "X" and its previous record values in Col3 should be "X" Else " "



So while checking this, how to skip the first values of Col2 (because no values will be there), from second values I have check whether negative or positive. If negative or equal to zero, the Col3="X" and also the above record Col3 values should be "X"

Any suggestions please
Appreciate your help in advance
Thanks a lot

[ArndW]

You have a variable called @INROWNUM that gives you the input row number, or you can use the stage variable "initial" value to give you a value for the first row.

In your case you need to use values from the previous row, so you will need to store these in stage variables.

I am not quite sure of your logic as described, though. the first part, "If the value in Col2 <=0 THEN Col3 = 'X'" is clear, but the else part is confusing me - do you want to use the previous value of Col3 or change it?

[krisp321]

Well, there wont be any value in Col3 to use. So its like generating an indicator "X" to process further.
So, let us say Col2 value of third record is -2, then the result of record2 and record3 of Col3 should be X

I have generated a surrogate key col along with this data. So in simple way to understand, record with surrogatekey=102 has col2 values<=0 then the records with surrogatekey 102 and 101 col3 should get updated or inserted as "X"

[ArndW]

While one can store values from previous records using stage variables, one cannot change output value from a previous record depending on a computation in the current one. You will need to re-sort your data in descending key-column in order to make this type of processing work.

[kandyshandy]

Try this way in server routine or px routine or a job

Your input is

Code: Select all

Col1                       Col2 
A                               -      
A    		-1     
A    		-3    
A    		 2        
A    		 5     
A    		 0   

First step is to get

Code: Select all

Col1                       Col2
A,                        -,-1,-3,3,5,0

Second is to build a third column based on the value of Col2, as shown below

Code: Select all

Col1                                   Col2                             Col3
A,                                 -,-1,-3,3,5,0                    X,X,X,3,5,0

Third is to replace 0 and it's previous comma delimited value to X as shown below

Code: Select all

Col1                                   Col2                              Col3
A,                                  -,-1,-3,3,5,0                   X,X,X,3,X,X

Last step is to replace rest of the numbers to space " " and do a horizontal pivot

You can achieve this using Vertical pivot, Index, Field, Convert etc