CODEWITHSUNDEEP
teradatainformatica-powercenter
Priyanka
Priyanka

Reputation: 5

Informatica: How to pick the sorted record from multiple rows or take the max on two columns together in Informatica

I have a requirement as below.

source:

   prod_id          DATE              Price      Count(Price)

   1          01-02-2017              100             1  

   1          01-02-2017              10             4          

   2          02-02-2017              50             1

   2          02-02-2017              60             1

I have data like this. Now I need to pick records with max(count(Price)) for every unique prod_id and date combination, but if Count(price) is same then it should take max(price) or sort the columns on the basis of Count(price) and price and pick the top record. I'm achieving this data by using an aggregator doing group by prod_id, date column and taking count on price and not a direct table. How can I achieve this? Any suggestions?

Thanks, Priyanka

Upvotes: 0

Views: 353

Answers (2)

dnoeth
dnoeth

Reputation: 60482

As you tagged Teradata, you can get it directly by adding

select .....
qualify
   row_number()
   over(partition by prod_id 
        order by Count(Price) desc, Price desc) = 1

to your current query.

Upvotes: 0

Samik
Samik

Reputation: 3455

Sort the records based on Count(Price) and Price in that order and use an aggregator with prod_id and date as key. Do not use any aggregate function. The aggregator will pass the last record for each group.

Upvotes: 2

Related Questions