8 Replies Latest reply on Aug 2, 2021 2:05 PM by Rudi Ribere

    Get MAX values

    Rudi Ribere New Member

      Hi

      I have the following source

      SRC

      product    Line   Order    Toto
      q1           10       20          3
      q1           30       300        6
      q1            5        2000      8

      q2           20       30          3
      q2           300     400        8

      q3           20       200        9
      q3           40       3000      5
      q3           10       7000      3
      q3           20       1200      4

       

      And a want to get on my target the lines whit the max Order for each product.

       

      TGT

      product    Line   Order    Toto

      q1            5        2000      8

      q2           300     400        8

      q3           10       7000      3

       

      Do you know how can i do that without using a select on the SQ?

       

      Thanks

        • 1. Re: Get MAX values
          Sachin Kumar Guru

          Please use aggregator transformation and see if it works.

          max(order) group by product.

          • 2. Re: Get MAX values
            Nico Heinze Guru

            In my opinion not the best idea because this way you ONLY get the maximum order value but not the other values.

             

            What I would do is to sort the data (using a Sorter transformation, not an ORDER BY in SQL) by Product followed by Order.

            Next use an Aggregator transformation with Sorted Input set. Group-By key is the Product. Simply forward all ports from the AGG to the target (without any grouping functions like LAST() or so, that's not necessary); if no grouping function is given in any output port, the AGG always forwards the last record per group (meaning per Product), and as data are sorted by Product followed by Order, the last record per Product is always the record with the highest Order value.

             

            Regards,

            Nico

            1 of 1 people found this helpful
            • 3. Re: Get MAX values
              JanLeendert Wijkhuijs Guru

              Hi,

               

              There is another option a little bit similar to Nico's suggestion.

              In the source qualifier in the Source Filter you can enter:

               

              1 = 1

              ORDER BY product, Order desc

               

              After the source qualifier in an expression you can create a variable port v_prev_product and in the expression column you put in product (the port).

              The v_prev_product must have the identical datatype and length compared to the product port.

              Above the v_prev_product you create an output port like FILTERING (datatype integer) in which you add the logic

              IIF(product <> v_prev_product, TRUE, FALSE)

               

              The next transformation should be a filter transformation in which the FILTERING port is the filter condition.

               

              Regards,

              JanLeendert

              1 of 1 people found this helpful
              • 4. Re: Get MAX values
                Nico Heinze Guru

                There's one ptential BUT to this suggestion (and that's the reason why i haven't mentioned this idea of having the data sorted by the source DB myself).

                 

                First it requires a relational database to be the source system. For example, for a flat file source you can't have the data be sorted by the Source Qualifier.

                 

                Second (and more important) it depends on the locale setting of the database whether this works.

                For example, if the source DB is SQL Server set to a German locale, then the following three city names will cause trouble:

                München (the German name of the city of Munich)

                Münster

                Munster

                In German language, the correct sort order is this:

                München

                Munster

                Münster

                However, PowerCenter expects the data to be sorted by the Unicode character codes, meaning in this order:

                Munster

                München

                Münster

                 

                Even more funny: if you work with DB2 on AIX or z/OS as a source database, then DB2 will deliver the data sorted by their EBCDIC codes. So, for example, the following IDs will be sorted in a way that PowerCenter cannot digest when a transformation is set to Sorted Input:

                123def

                ABC456

                abc789

                PowerCenter needs these records in this order.

                But Db2 on AIX or z/OS will deliver the records in the following order:

                ABC456

                abc789

                123def

                 

                You see, you can go on funny adventures with ORDER BY clauses.

                 

                Regards,

                Nico

                • 5. Re: Get MAX values
                  Rudi Ribere New Member

                  it's still not working i think that i have a problem on the expression

                   

                  Ports on the expression :

                  PRODUCT

                  LINE

                  ORDER

                  TOTO

                  v_prev_product   (PRODUCT)

                  v_filter   (IIF(PRODUCT = v_prev_product, 'TRUE', 'FALSE')

                   

                  on the target i have all the lines but ordered (im doing the order by on the SQ), the filter is always geting the value true.

                  do you know what im doing wrong?

                  • 6. Re: Get MAX values
                    JanLeendert Wijkhuijs Guru

                    Hi Rudi,

                    The v_prev_product MUST be the last port in the expression.
                    In my previous post I mentioned the output should be above this port.
                    The reason for this is simple basically PowerCenter is updating the variables in order of the ports.
                    In this case you want to process the difference in the current data compared to the previous record therefore the v_prev_product must be the last port which will be updated.

                     

                    Ports on the expression :

                     

                    PRODUCT

                    LINE

                    ORDER

                    TOTO

                     

                    v_filter              (IIF(PRODUCT = v_prev_product, 'TRUE', 'FALSE')  variable port

                    out_filter           v_filter                                                                           output port

                     

                    v_prev_product  (PRODUCT)                                                               variable port



                    Hope this helps.

                    Regards
                    JanLeendert





                    1 of 1 people found this helpful
                    • 7. Re: Get MAX values
                      JanLeendert Wijkhuijs Guru

                      Hi Nico,

                      Thanks a lot for the clarification, I wasn't aware of it and will definitely keep it in mind in future developments.

                      I must say in this case it is not of great importance.
                      The goal of the sorting in this case is to group the products together with the descending sorting on the numeric value which shouldn't be a problem.

                      Regards,

                      JanLeendert

                      • 8. Re: Get MAX values
                        Rudi Ribere New Member

                        thanks JanLeendert it works fine