Skip navigation
Contact Us
2197 Views 13 Replies Latest reply: Nov 29, 2012 7:06 AM by will i RSS
Mohammed Omair Newbie 11 posts since
Jul 10, 2012
Currently Being Moderated

Nov 25, 2012 7:32 AM

Infa powercenter 9.1- Aggregator and active lookup

For the following Data:

 

col1col2col3
11112
11112
22123
22122

 

 

 

Using an aggregator- What will be the output if the Group by port is col1 and output port is min of col3.

 

Further clarification:

The above data is looked up from a table using an active lookup i.e; the above data is for one input row and the output must single row.

col1col2col3
11112
11112
22123
22122
  • Joe . Novice 21 posts since
    Nov 20, 2012
    Currently Being Moderated
    Nov 25, 2012 10:38 AM (in response to Mohammed Omair)
    Infa powercenter 9.1- Aggregator and active lookup

    col1 col2 col3

    1      11   12

    2       21   22

     

     

    In the aggregator cache, corresponding to value col1=1 there will be two rows. The one with min(col3) will be the output row. Hence the 1--11--12

     

    For the second value os col1=2 again there are two rows. But the min(col3) value is selected.

      • Joe . Novice 21 posts since
        Nov 20, 2012

        In that case you may uncheck the group by port i.e. do not group by any column and put min(Col3) in the agg expression.

         

        now we have two rows that have min col3 value i.e col3=12. The top most row in the aggregate cache will be considered.

      • sasi ramesh Master 1,110 posts since
        Oct 2, 2010
        Currently Being Moderated
        Nov 26, 2012 10:01 PM (in response to Mohammed Omair)
        Infa powercenter 9.1- Aggregator and active lookup

        Hi Mohammed,

         

        If you have used active lookp ,then it will return mutiple rows based on the match .If you want the single row and want the minimum of col3 then

         

        Use dynamic lookup and add an order by clause in the lookup override ,then select to bring the first values on multiple match ( which will bring the minimum)

         

         

        Lookup overide statement:

         

        select col1, col2, col3 from table X order by col3 --

         

        Regards

        Sasiramesh

          • azhar ahamad Newbie 3 posts since
            Nov 15, 2012

            2 scenarios - you do agg first , then

             

            col1     col2    col3

            1          11       12

            1           21       22   , because your criteria for gb is min(col3).

             

            You do active look-up with this result set, based on matching condition, ideally you will get only 1 out-put, but you do active only to get all multiple matches, so assuming you have multiple matches, the out-put need to be worked accordingly, based on what you will do with multiple matches.

             

            but if you do active first, and get multiple matches, and then if you do agg, then above o/p is what you will get again.

             

            to your point of getting only 1 out-put, simple do min(col3) , and do not define group-by and you will get only 1 record.

             

            in reply to your reply of dates:

             

            you can do this in expressions transf , since it is row based , it will check for each c_id, do this

             

            iif( to_date(to_char(col3),format) > sysdate , 'Y' , 'N' ).

             

            hope this helps. feel free to ask a question, if you need more clarification.

             

            thanks,

            azhar.

    • sasi ramesh Master 1,110 posts since
      Oct 2, 2010

      Hi Mohammed,

       

      Sorry to pester you.Still i couldn't understood the requirements.

      As per my understanding you want the output data as below

       

      sk2   col1(c_id) col2(opn_dt_sk) col3(close_Dt_sk)  (minCol3)     Flag

      1             1             20121011        20121101            20121101    Y

      1            2               20121110       20121111            20121111     Y

       

       

      Then use aggregator after lookup and find the minimum by slecting the group by on C_id ,

      then theoutput will be restricted to 2 records ..one for c_id 1 and one for c_id 2.

       

      In expression check the minimum colum value with the current date and set the flag indicator.

       

      Please post the input and expected results ,if my assumptions is incorrect.

       

      Thanks

      Sasiramesh

        • azhar ahamad Newbie 3 posts since
          Nov 15, 2012
          Currently Being Moderated
          Nov 28, 2012 11:33 AM (in response to Mohammed Omair)
          Infa powercenter 9.1- Aggregator and active lookup

          From my understanding, you need to check

           

          sk2   col1(c_id) col2(opn_dt_sk) col3(close_Dt_sk)  (minCol3)     Flag

          1             1             20121011        20121101            20121101    Y

          1            2               20121110       20121111            20121111     Y

           

          col1 ( 1 , 2 ,  ....) for group sk2 - 1 ???

           

          for this,

           

          in expressions, after your group by , create 2 variable ports, new and old .

           

          in IIF condition , everytime sk2_old = sk2_new , check for col3 against sysdate , and get the minimum value  ...

           

          when they don't match, assign it to new value. when your are ready with the logic in variable port, assign it to o/p port.

           

          this way, for every group , you will get only 1 out-put, but be vary that , you might check with your requirment, which col1 valur you should populate , in case col3 values are same , either 1 or 2.

           

          sk2   col1(c_id) col2(opn_dt_sk) col3(close_Dt_sk)  (minCol3)     Flag

          1             1             20121011        20121101            20121101    Y

           

          hope this helps.

           

          -Azhar.

        • Joe . Novice 21 posts since
          Nov 20, 2012

          you can compare rows using the MD5 function in the expression transformation. Which generates a hexadecimal code for every row. For similar row, this hexadecimal code will be the same and vice versa. This can be checked in an IIF () and the flag can be set

        • will i Novice 45 posts since
          Jul 25, 2010

          Hi,

          try this...

          -after Lookup--sort on (close_dt_sk) -desc----(the last record will be the min date)

          -In aggregator -- keep (close_dt_sk)- as I/p port and create an o/p- last(close_dt_sk) - with group by on (close_dt_sk) (which will be selected by default).

          -filter out by close_dt_sk < sysdate.

           

          Thanks

          Will

More Like This

  • Retrieving data ...

Bookmarked By (0)