Unique Count

Hi All,

I am trying to transform the data below (please see both tables). I am able to get the first two columns in the transformed table by using group by and distinct count. I am unable to come up with a clean logic to get column 3. Can you help?

Data:

ID1	ID2	ID3
A	1	1
A	1	1
A	2	0
A	2	0
A	3	1
A	3	1
A	3	1
A	4	0

Transformation:

ID1	ID2	ID3
A	4	2

Transformation

Accepted answers

jdunkerley79

You will have to compute the D.ID value separately and then join it back together.

Sample attached

Aggregrates.yxmd

All comments

jdunkerley79

I would suggest transposing, summarising and cross tabbing to get distinct.

Quick sample attached

CrossTab Summarise.yxmd

dataguy

Thanks so much for your quick response. I really appreciate it. My apologies for providing incomplete information. Let me provide the additional columns and that should help you formulate the complete solution. There are some columns on which I want to do a count distinct and on some columns sum.

Data:

A. ID	B. ID	C. ID	D. ID	E. Num	F. Num
X	1	1	1	100	200
X	1	1	1	100	200
X	2	2	0	200	300
X	2	2	0	300	400
X	3	1	1	500	600

Transformation:

A. ID	B. ID	C. ID	D. ID	E. Num	F. Num
X	3	2	2	1200	1700

In this case, the column D. ID was the one for which you provided the solution.

A.ID -> Group BY

B.ID -> Count Distinct Not Null

C. ID -> Count Distinct Not Null

D. ID -> ??? some combination of transpose and cross tab ???

E.ID -> Sum

F. ID -> Sum

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703