Search code examples
etlinformatica-powercenter

Transformations with desired values

I'm trying a process with powercenter designer but I do not get the desired objective.

I have these initial data:

CODE    CODE2   OPTION
001     A        89
001     A        55
001     A        12
002     B        25
002     A        59
025     A        44

I have to get it for code to do the following: if there are several records per CODE then you have to put the value 1111 in the OPTION2 field to the record with the highest value in the OPTION field, if there is only one record in CODE it also puts the value 1111. I do this by making an SORTER transformation in powercenter, not complicated. What I can not do is the next step. The second record with the highest value in the OPTION field corresponds to the value of the first field and so on.

OUTPUT:

CODE    CODE2   OPTION  OPTION2
001      A       89     111111
001      A       55       89
001      A       12       55
002      A       59     111111
002      B       25       59
025      A       44     111111

How could I get this? What transformations should I use?

Thanks! ^^

Solution

  • You can sort it by code and descending order of option. Then in an expression variable hold the value for the previous record's value in a variable.

    v_OPTION2 = IIF(ISNULL(v_PREV_CODE) OR CODE != v_PREV_CODE, 
              111111, 
              v_PREV_OPTION
            )
out_OPTION2 = v_OPTION2
v_PREV_OPTION = OPTION
v_PREV_CODE = CODE