4 Replies Latest reply on Dec 3, 2019 12:59 PM by Washington Filho

    Rownumber at IPC Designer not working as expected

    Washington Filho New Member
    Washington Filho Dec 2, 2019 7:14 AM

      Hello everyone.

       

      I have an issue here when I try to make the Rownumber over partition on Designer IPC.

       

      Basicaly, I dont know how to keep in a variable field of an expression session the previews data to compare with the current one.

       

      I've search a lot how to replicate Rownumber function from SQL on IPC Designer with the transformations, and at least most of the answares said to do the following logic in an expression transformation:

       

      row = some_counter_field

      FIELD

      FIELD_CHECK = IIF(FIELD = OLD_FIELD, row + 1,1)

      OLD_FIELD = FIELD

       

      In theory, this way I could get the same logic the "Rownumber partition by" does on SQL, but when I run it, even sorting the data and making sure there is duplicate rows together, the output value is always "row + 1" (true), so it means that the variable field OLD_FIELD is not keeping the previews data, but actualy are updating by getting the current FIELD.

       

      So, for exemple:

       

      When I run the expression on Designer, I get the following output at the field ROWNUMBER_COUNT:

       

      ...............................................................

      ROWNUMBER_COUNT | COLUMN_A |

      1                                      | AB                |

      2                                      | AC                |

      3                                      | AC                |

      4                                      | AC                |

      5                                      | AD                |

      ...............................................................

       

      What I expected to get as output would be:

      ...............................................................

      ROWNUMBER_COUNT | COLUMN_A |

      1                                      | AB                |

      1                                      | AC                |

      2                                      | AC                |

      3                                      | AC                |

      1                                      | AD                |

      ...............................................................

       

      I hope I made myself clear.

      Thanks.

        • 1. Re: Rownumber at IPC Designer not working as expected
          Nico Heinze Guru
          Nico Heinze Dec 2, 2019 10:23 AM (in response to Washington Filho)

          Unfortunately no (not 100% clear to me), but that's not your fault, that's simply due to the fact that I refuse to program in SQL, I prefer building logic in PowerCenter. This leads to me not knowing what RowNumber Over Partition means.

          Could you please explain to me in plain text (maybe with a small sample set of data) what you exactly mean and how this works in your DBMS (which DBMS do you use?)? That should enable me (and maybe other people, too) to find out how to help you here.

           

          Regards,

          Nico

          • 2. Re: Rownumber at IPC Designer not working as expected
            Washington Filho New Member
            Washington Filho Dec 2, 2019 11:17 AM (in response to Nico Heinze)

            Hi Nico.

             

            Thanks for reply.

             

            I use SQL Server 2016.

             

            Well, the Rownumber Over Partition basecaly makes the following:

             

            You choose one or more fields to be your key and based on your sorter rule you make a ranking that restarts from 1 everytime it gets in the end of the partition.

             

            For example, if you think about a table with 100 records, you can enumerate them sice 1 to 100. So you have the record/row 1, the record/row 2, record/row 3 etc.

             

            So every row has a rownumber (row 1, row 2, row 3 until the 100º row (in the example), which will be the rownumber 100).

             

            The "Over Partition" means that you want to do the same enumerating process, but partitioning the rows (the partition is the field(s) you have chosen to be it).

             

            So let's suppose I have the records below sorted by FIELD_I_CHOOSE_AS_PARTITION and DATE_LOAD in this order:

             

            ........................................................................................

            Field_I_Choose_as_Partition | DATE_LOAD | Status        |

            ABC                                        | 12/02/2019    | INACTIVE |

            ABC                                        | 12/01/2019    | ACTIVE     |

            ABC                                        | 11/02/2019    | INACTIVE |

            ACC                                        | 12/02/2019    | ACTIVE     |

            ACC                                        | 12/01/2019    | INACTIVE |

            ADA                                        | 12/02/2019    | ACTIVE      |

            ADA                                        | 12/01/2019    | INACTIVE |

            ADB                                        | 12/02/2019    | INACTIVE |

            .........................................................................................

             

            So, my partition is FIELD_I_CHOOSE_AS_PARTITION and my sorter rule is FIELD_I_CHOOSE_AS_PARTITION and DATE_LOAD.

             

            Now the logic is to enumerate each row and restart the enumeration in 1 when a partition ends and begins another, like bellow:

             

            ........................................................................................

            ROW_NUMBER  | Field_I_Choose_as_Partition | DATE_LOAD | Status        |

            .........................................................................................................................

            1                           | ABC                                        | 12/02/2019    | INACTIVE |

            .........................................................................................................................

            2                           | ABC                                        | 12/01/2019    | ACTIVE     |

            .........................................................................................................................

            3                           | ABC                                        | 11/02/2019    | INACTIVE  |

            .........................................................................................................................

            1                           | ACC                                        | 12/02/2019    | ACTIVE     |

            .........................................................................................................................

            2                           | ACC                                        | 12/01/2019    | INACTIVE  |

            .........................................................................................................................

            1                           | ADA                                        | 12/02/2019    | ACTIVE      |

            .........................................................................................................................

            2                           | ADA                                        | 12/01/2019    | INACTIVE  |

            .........................................................................................................................

            1                           | ADB                                        | 12/02/2019    | INACTIVE  |

            .........................................................................................................................

            1                           | ADC                                        | 12/02/2019    | ACTIVE      |

            ..........................................................................................................................

             

            This is most used when you have a table with some fields duplicated and you need the most recent record for example but you cant simply group by them bcz there is a lot of different fields.

            So in the above example I could select the most recent status by selecting all records where ROW_NUMBER = 1 (that is te most recent record).

             

            I know in my example we could just use a group by with a max function, but in other cases thats not possible, so you have to do the ROWNUMBER OVER PARTITION.

             

            On IPC, I can reproduce the rownumber over partition if I can keep the previews data, so I can sort before and then compare the current data with the previews one, and then make the ranking.

             

            Sorry bcz im not very good by explaning, but I think you're gonna understand my explanation easily. If you dont, I can get a real example for you.

             

            Thanks again.

            • 3. Re: Rownumber at IPC Designer not working as expected
              Nico Heinze Guru
              Nico Heinze Dec 3, 2019 9:49 AM (in response to Washington Filho)

              Thanks for the explanation, makes perfect sense.

               

              So I understand that you have implemented this ROWNUMBER OVER PARTITION by using at least two variable ports: one to hold the value from the previous record (in your example FIELD_I_CHOOSE_AS_PARTITION) and one as a flag set to 1 when the current value belongs to a different partition than the previous record.

              Now there's one catch: both these ports must be variable ports. It won't work to assign this IIF() value to an output port, you must assign it to a variable port.

              Why?

              Due to the order of execution of ports in PowerCenter:

              - First all input ports from top to bottom,

              - then all variable ports from top to bottom,

              - finally all output ports from top to bottom.

              If you assign this IIF() result to an output port, then all variable ports already have been evaluated, meaning OLD_FIELD will already the value of FIELD. Meaning there's no difference between FIELD and OLD_FIELD at this moment.

               

              Hence my question:

              Did you assign the IIF() result to a variable port or an output port?

              If it's an output port, then introduce an additional variable port before OLD_FIELD.

              If it's a variable port, make sure it's located above OLD_FIELD in your port list.

               

              My apologies if this is all trivial to you; trust me, there are many people who find this functionality quite confusing.

               

              Regards,

              Nico

              • 4. Re: Rownumber at IPC Designer not working as expected
                Washington Filho New Member
                Washington Filho Dec 3, 2019 12:59 PM (in response to Nico Heinze)

                That's it Nico.

                 

                Im really thankful for your help.

                It worked perfect now.

                 

                I was doing almost everything right.

                 

                That's what I was doing wrong: I was putting the variable and output fields between input fields.

                 

                Then I moved the variable and output fields to the end of the transformation (So I got first input, variable and in the very end, the outputs), just as you said, and it worked fine.

                 

                Thanks again.

                My best regards.