For the following Data:
Using an aggregator- What will be the output if the Group by port is col1 and output port is min of col3.
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.
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.
But you haven't considered the second part of my question. I need only one output row out of it ( since I am using an active lookup, the above data corresponds to only one input row).
I need only one output row ie; from the above I need the Min of COl3.
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.
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 --
To help you ettter understand, Let me give you some real feel of data. After the active lookup and aggregator I will get this output
col1(c_id) col2(opn_dt_sk) col3(closed_Dt_sk)
1 20121011 99999999
2 20121111 20121127
and then I need to check for col3>curr_date(20121127) for each c_id. If any of c_id for i/p row doesnot satify this condition then the o/p is Y else N.
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.
Here is some detailed data for better understanding of the requirement.
1. Lookup condition: src_tbl.sk1= tbl2.sk2 ( using active lookup to get all matching records fro each sk2)
2. I need to find the min close_dt_Sk for each 'c_id'
3. I need to check if the min( close_dt_Sk) for each c_id is < curr date. If so, then the ouput port is set to 'N' else to 'Y'.
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.
You are correct upto some extent.
I need the 'flag' to be set for sk2=1, not for each of c_id. Can I achieve this using an exptrans?
Can you explain me how I can check for the min(close_dt_sk) for two different groups using an expression. Because we only get a single input port into the exptrans even though there may be multiple rows coming. How can I compare rows?
From my understanding, you need to check
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
-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.