Unique Count

[dataguy]

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

[jdunkerley79]

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

Quick sample attached

[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

[danrh]

You should be able to do this with only the Summarize tool:

Count Distinct on column D isn't giving you 2 in this situation?  See attached and let me know if I've set something up differently than you have it.

[jdunkerley79]

@danrh has provided the much simpler solution! 

If you have dynamic sets of columns then my approach would work but if you have known set, then his is better.

If you do need to cope with different sets then you can use filter tools to aggregate with different methods. Happy to put a sample together if useful.

[dataguy]

Thanks for the reply. I changed the data set to better illustrate this example. The calculation of D.ID is linked to B.ID. Essentially, D.ID is a flag ( 1 or 0) that contains information about B.ID. However, I want to sum the D.ID for every B.Id only once. For example, B.ID shows up four times. However, I want D.ID to show 3 because only three B.ID have value. I would appreciate a sample workflow.

Data:

A. ID	B. ID	C. ID	D. ID	E. Num	F. Num
X	10	100	1	1000	2000
X	10	100	1	1000	2000
X	10	500	1	1000	2000
X	20	200	0	2000	3000
X	20	200	0	3000	4000
X	30	300	1	5000	6000
X	40	400	1	6000	7000

Transformation:

A. ID	B. ID	C. ID	D. ID	E. Num	F. Num
X	4	5	3	19000	26000

[jdunkerley79 · Accepted solution]

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

Sample attached

[dataguy]

Thanks!