Count set of unique pairs

[shivraj_r]

Hi all,

I'm trying to count the number of times a unique pair occurs in my dataset and wondering how to do this in Alteryx? I've used the sumproduct function on Excel and works fine. Attaching a sample here

Old	New	Count of unique pairs (output)
a	b	2
b	d	1
d	g	1
g	a	1
a	b
b	a	2
a	g	1
g	b	1
b	a

[Kenda]

Hey @shivraj_r!

I would recommend creating a new field with a Formula tool with the following expression:

[Old]+[New]

Then, use a Summarize tool to GroupBy your new field and Count your new field.

[shivraj_r]

@barnesk - Thanks that works great!! But I was hoping to find out if there is a way to do this without concatenating?

[Kenda]

@shivraj_r Do you have the CReW macros? You could use the Only Unique tool then use a Summarize tool. See the attached workflow.

[shivraj_r]

I, unfortunately do not have the CReW macros! When I run the file it says there is a compatibility issue - I'm running alteryx 10.6 as part of a corporate license! That being said - only unique tool does look promising! Thanks - at least for now I'm going put a delimiter in before the count so I can separate the columns later hopefully!

[Kenda]

@shivraj_r Can you unzip the file I attached? If you can, do that. Then right click on the .yxmd file and open with Notepad. Then change the yxmdVer number on line 2 to the version you are using. Save then try to open again.

[shivraj_r]

@Kenda  Did that!  Unfortunately -  the same compatibility error!

[haroon_sa]

Hi @Kenda

CReW macro unique tool is working good but it seems working same as the unique tool which is in preparation part.

can you explain me, What is the difference between them?

it will be very helpful for me

[MarqueeCrew]

@haroon_sa,

The CReW macro outputs records where the Key Field(s) are truly unique.  The standard unique tool will output the first record key (FIFO) which it encounters.  If you have 2 or more records with the same key, it outputs just the first record.  The Unique Only will output keys where 1 and only 1 key exists.  If there are duplicates of a key, then all the records are considered duplicates.

Cheers,

Mark

[Kenda · Accepted solution]

@shivraj_r Looking at this again, I realize I had a brain fart yesterday.. What if you just try adding a Summarize tool after your input without the Unique tool? GroupBy your Old and New fields then add a Count as well.

That's the awesome thing about Alteryx - so many ways to get to the same result!