14 Replies Latest reply on Jan 11, 2022 1:40 PM by Nico Heinze

    Dev logic, Comparison between rows and deciding the winner record.

    Md Ahmed Aziz Guru

      Hi Team,

       

      Can anybody help me build the logic,

      We have 4 column that needs to be compare, based on given criteria, it should give us the result and we can mark it a flag(W) , we will be having 2,3 records in every fetch which the comparison needs to be done,

       

       

      ROWID_OBJECT ROWID_OBJECT_MATCHEDORIG_ROWID_OBJECT_MATCHED PAYOR PAYOR PAYOR ORDER_COUNT ORDER_COUNT ORDER_COUNTPERSON_COUNT PERSON_COUNT PERSON_COUNT CUST_AFFLTN_COUNTCUST_AFFLTN_COUNT
      5510          5511          5511          NNN14111133355
      5508          5509          5509          NNN88810022
      5507          5506          5505          NNN8102012423
      55055506          5506          NNN20101042253

       

       

      >> if payor is N, then which ever is having highest order count will get flag as winner, suppose if 2 record are having same order count then it moves to next column comparison between contact_count which ever has highest contact_count it wins, if again contact_count is also same then it goes to cust_affl_count with highest count then particular row should win and we will assign it as a flag 'W' and rest of the record as 'F'

       

       

      At the end final result after all comparison it should say record 3 as 'W'

       

      Thanks,

        • 1. Re: Dev logic, Comparison between rows and deciding the winner record.
          Nico Heinze Guru

          Filter out all records having Payor != 'N'.

          Sort the remaining records by Order_count (ascending), Contact_Count (ascending), and  cust_affl_count (ascending).

          Use an Aggregator to get the last record from the whole input stream. This is the one with either the single highest Order_count value (due to Order_count being the first sorting criterion) or, in case of several records with the same Order_count value, the one with the single highest contact_count or, in case of several records with the same contact_count value, the one with the highest cust_affl_count.

           

          Regards,

          Nico

          • 2. Re: Dev logic, Comparison between rows and deciding the winner record.
            Md Ahmed Aziz Guru

            Thank you Nico, Let me try to implement

            • 3. Re: Dev logic, Comparison between rows and deciding the winner record.
              Md Ahmed Aziz Guru

              Hi Nico,

               

              below is the exact table where the comparison will happen each row will have like this 3 column to compare. then the final result will be like which ever (rowid_object, rowid_object_matched, orig_rowid_object_matched) is having highest count based on that winner is decided and in target will mark it as W.

               

               

               

              Thanks,

              Aziz

              • 4. Re: Dev logic, Comparison between rows and deciding the winner record.
                Nico Heinze Guru

                As mentioned:

                Start by using a Filter to throw away all records with PAYOR != 'N'.

                Send all remaining records through a Sorter transformation, sorting by ORDER_COUNT, CONTACT_COUNT, and CUST_AFFL_COUNT.

                Use an Aggregator without any Group-By port to deliver the last record from the Sorter's output.

                Use a Joiner with Sorted Input to join the Aggregator's output record with the original data stream, based on ORDER_COUNT, CONTACT_COUNT, and CUST_AFFL_COUNT (outer join so that all original records and the matching data from the AGG are joined). This way your data stream will have all ports from the AGG as NULL for all records but the one which you want to mark as 'W'.

                Now set up an Expression transformation after the Joiner. In this EXP, check whether the ports from the AGG passed through the Joiner are NULL; if so, the current record is one of the 'F' records; if the ports from the AGG for the current record are not NULL, then this is the 'W' record.

                 

                Anything there which I left unclear?

                 

                Regards,

                Nico

                • 5. Re: Dev logic, Comparison between rows and deciding the winner record.
                  Md Ahmed Aziz Guru

                  Hi Nico,

                   

                  Thank you very much for your brief explanation its very clear to understand. Really appreciate you efforts,

                   

                  after implementing the above scenario the results are not as expected because the sorter is sorting it based on entire column, but our comparison is between 1 column to another column, I think I made it confused by 3 set example. suppose let's consider only one example shown below,

                   

                  once we pull the data from DB to IDQ layer assume below table as ports in data viewer same like this the data will be at physical data object, now if you see there are two rowid both have different order count,  the end result should be as rowid-5510 is having high order count so winner should be rowid- 5510.

                   

                   

                  ROWID_OBJECT

                  ROWID_OBJECT_MATCHED

                  ORDER_COUNT

                  ORDER_COUNT

                  PERSON_COUNT

                  PERSON_COUNT

                  CUST_AFFLTN_COUNT

                  CUST_AFFLTN_COUNT

                  5510         5511         14113355

                   

                   

                   

                   

                   

                  2nd Scenario :

                   

                  If both rowid have same order count then it needs to check person count, as in below scenario both order count is same then compare person count, here 5508 has person count 1, so 5508 becomes winner record.

                   

                   

                  ROWID_OBJECT

                  ROWID_OBJECT_MATCHED

                  ORDER_COUNT

                  ORDER_COUNT

                  PERSON_COUNT

                  PERSON_COUNT

                  CUST_AFFLTN_COUNT

                  CUST_AFFLTN_COUNT

                  5508         5509881022

                   

                   

                  Thanks

                  Aziz

                  • 6. Re: Dev logic, Comparison between rows and deciding the winner record.
                    Nico Heinze Guru

                    What's the result you're getting from the Sorter? Can you please post some sample input (e.g. the two records above) and the order delivered by the Sorter transformation?

                    Can you please also post a screenshot of the ports tab of the Sorter transformation?

                     

                    Ah, one more idea: I'm not an IDQ guy, I'm a PowerCenter guy. And as far as I recall, IDQ often works quite differently from PowerCenter, e.g. by "optimising" ports in Expression transformations. And that can lead to wrong results when applying mapping logic which works well in PowerCenter.

                    Can you please turn off mapping optimisation for this mapping and run again? And post the output of the Sorter transformation?

                     

                    Regards,

                    Nico

                    • 7. Re: Dev logic, Comparison between rows and deciding the winner record.
                      Nico Heinze Guru

                      Sorry, but you confused me. What exactly are you trying to achieve? For me it seems as if you were asking two completely different questions.

                      Can you please post a short but complete set of input records plus requested output?

                       

                      Thanks,

                      Nico

                      • 8. Re: Dev logic, Comparison between rows and deciding the winner record.
                        Md Ahmed Aziz Guru

                        Hi Nico,

                         

                        Sorry for late reply I was assigned with different work now resuming this back,

                         

                        Our Requirement is, in below screenshot we have 1 row which has 3 rowid column , out of these 3 rowid column  1 rowid column will be winner, to decide the winner

                        1st condition:  if any rowid is having PAYOR as 'Y' it will be winner, if not

                        2nd condition: if all PAYOR are 'N' as show in below screenshot then it needs to compare order_count which ever rowid column is having highest count that will be winner.

                         

                        for eg : 101266 order count is 7 ; 101265 order count is 11 ; 101264 order count is 21;

                         

                        (if all order count is same then next comparison it will check is highest person count)

                         

                         

                        Thanks

                        Aziz

                        • 9. Re: Dev logic, Comparison between rows and deciding the winner record.
                          Nico Heinze Guru

                          It would have been easier if you could have posted that right from the start.

                           

                          Anyway. The logic is actually simple, you just translate the steps above into a variable port (which "indexes" the correct ROWID value) and then "translate" this index value into the right result.

                          But in order to keep things easy to read and to understand and maintain, I suggest that you don't try to set up everything in one single expression term; that can be done, but it's extremely difficult to read and maintain.

                           

                          In order to keep things as simple and understandable as possible, I would set up several variable ports. I'll list them separately to make it easier to see how the whole thing works.

                          Let's start with the first block:

                          - v_cmp_1_2 (type Integer),

                          - v_cmp_1_3 (type Integer),

                          - v_cmp_2_3 (type Integer),

                          - v_count_winner (type Integer),

                          - v_are_counts_identical (type Integer).

                          Here's the expression term for v_cmp_1_2:

                            ORDER_COUNT_1 > ORDER_COUNT_2

                          (that's the short version of "IIF( ORDER_COUNT_1 > ORDER_COUNT_2, TRUE, FALSE)" )

                          Here's the term for v_cmp_1_3:

                            ORDER_COUNT_1 > ORDER_COUNT_3

                          And the term for v_cmp_2_3:

                            ORDER_COUNT_2 > ORDER_COUNT_3

                          Now the term for v_count_winner:

                          Decode( TRUE,

                            v_cmp_1_2, IIF( v_cmp_1_3, 1, 3),

                            IIF( v_cmp_2_3, 2, 3)

                          )

                          This way v_count_winner will be 1 if ORDER_COUNT_1 is the largest number, 2 if ORDER_COUNT_2 is the largest number, and 3 if ORDER_COUNT_3 is the largest number.

                          Finally the term for v_are_counts_identical:

                            ORDER_COUNT_1 = ORDER_COUNT_2 AND ORDER_COUNT_1 = ORDER_COUNT_3

                           

                          Here's the next "block" of variable ports:

                          - v_pers_1_2 (type Integer),

                          - v_pers_1_3 (type Integer),

                          - v_pers_2_3 (type Integer),

                          - v_pers_winner (type Integer).

                          The term for v_pers_1_2:

                            PERSON_COUNT_1 > PERSON_COUNT_2

                          The term for v_pers_1_3:

                            PERSON_COUNT_1 > PERSON_COUNT_3

                          The term for v_pers_2_3:

                            PERSON_COUNT_2 > PERSON_COUNT_3

                          The term for v_pers_winner:

                          Decode( TRUE,

                            v_pers_1_2, IIF( v_pers_1_3, 1, 3),

                            IIF( v_pers_2_3, 2, 3)

                          )

                          You can see the symmetry between the "count" block and the "person" block (except for the flag port indicating whether the three numbers are identical).

                           

                          And now for the final variable port v_winner_index (type Integer) with this expression term:

                           

                          Decode( TRUE,

                            PAYOR = 'Y', 1,

                            PAYOR1 = 'Y', 2,

                            PAYOR2 = 'Y', 3,

                            v_are_counts_identical, v_pers_winner,

                            v_count_winner

                          )
                          Any more questions? Please ask.
                          BTW before I forget it: disable optimisations for the Expression holding these variable ports, otherwise you may run into errors.
                          (Info for PowerCenter users: this optimisation doesn't exist in PowerCenter).
                          Regards,
                          Nico
                          1 of 1 people found this helpful
                          • 10. Re: Dev logic, Comparison between rows and deciding the winner record.
                            Md Ahmed Aziz Guru

                            @Nico Heinze,

                             

                            Thank you very much for such a clear understanding, really appreciated your efforts for it. I will try the above logic and it sounds like a plan

                             

                            Thanks,

                            Aziz

                            • 11. Re: Dev logic, Comparison between rows and deciding the winner record.
                              Md Ahmed Aziz Guru

                              Hi Nico,

                               

                              what will be our Output port which shows results??

                               

                              Thanks,

                              • 12. Re: Dev logic, Comparison between rows and deciding the winner record.
                                Nico Heinze Guru

                                As I am a PowerCenter guy and have almost no knowledge about IDQ, I am curious as well whether this works as described.

                                 

                                Cheers,

                                Nico

                                • 13. Re: Dev logic, Comparison between rows and deciding the winner record.
                                  Md Ahmed Aziz Guru

                                  Yes your logic worked, but few addition done we need to create one more port as ''OUTPUT' and have our variable port in it, and using order count, person count logics separately in different expression. After each comparison using router to route winner and left over comparison separately.

                                   

                                  Thank you for your quick response and follow ups.

                                  • 14. Re: Dev logic, Comparison between rows and deciding the winner record.
                                    Nico Heinze Guru

                                    BTW one more detail hint: in PowerCenter there's a function CHOOSE(); you would use it as in the following example for your output port (which I indeed forgot to mention, sorry for that):

                                    CHOOSE( v_winner_index, ROWID_1, ROWID_2, ROWID_3)

                                     

                                    Maybe IDQ provides a CHOOSE() function with the same usage? That would make life easy for the output port.

                                     

                                    And yes, I did leave out a couple of details. Shame on me. Glad that you were able to find the answers you needed.

                                     

                                    Regards,

                                    Nico