关闭

Informatica中实现count(distinct)

1062人阅读 评论(0) 收藏 举报
分类:
Thanks for your response. Here is the sample data information: 

A id1 $200 
A id1 $300 
A id2 $150 
B id3 $100 
B id4 $20 

I want the following in the output: 
Name distinct-Count Totalamt 
A 2 $650 

B 2 $120 


One way to do this is to use 2 aggregators. The First aggregator group and sum amt by Name and ID. 
On the second aggregator group by Name and count id and sum amt.

So if I remove the duplicate row in the Sorter I will get incorrect distinct-count and totalamt for "A".

There are two different ways. 

1) Suggested by Manas - to use two aggregates. 
2)Flaging the record as 1 and 0 before aggregate. For this, you should have sorted data on name and id. 
If you have sorted data coming from source, try 2nd option as given below. 
In expression transformation before aggregator. 

IN_ID 
v_ID_CNT = IIF(ISNULL(v_ID) Or IN_ID!= V_ID,1, 0) 
O_ID_CNT=V_ID_CNT 
V_ID=IN_ID 

O_ID_CNT will go in to your aggregator. 

You need to create one more port in aggregator for SUM(O_ID_CNT) which will return the distinct count of IDs. 
(No change in GROUP BY columns)

0
0

猜你在找
【直播】机器学习&数据挖掘7周实训--韦玮
【套餐】系统集成项目管理工程师顺利通关--徐朋
【直播】3小时掌握Docker最佳实战-徐西宁
【套餐】机器学习系列套餐(算法+实战)--唐宇迪
【直播】计算机视觉原理及实战--屈教授
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之矩阵--黄博士
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之凸优化--马博士
【套餐】Javascript 设计模式实战--曾亮
查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1912808次
    • 积分:17439
    • 等级:
    • 排名:第547名
    • 原创:297篇
    • 转载:330篇
    • 译文:0篇
    • 评论:107条
    最新评论