Hi, I am trying to get a count of how many unique applications there are for certain groups from the data below FrequencyNameApplicationDailyAExcel, PowerPoint DailyAExcelDailyAAlteryx, ExcelMonthlyAExcel, WordMonthlyAPowerPointDailyBExcelDailyBExcel I want to count the number of unique applications grouped by frequency and name e.g like below - any suggestions of how I could do this please? FrequencyName Number of ApplicationsDailyA3DailyB1MonthlyA3

@hellyarsused a slightly different approach than me - that worfklow concatenates all values into one list (via summarize tool) - and then breaks down - whereas I started by breaking down the values. The key elements are separating to rows by comma, data cleansing and then using a summarize. I used count/distinct in my workflow @hellyars uniqued and then counted - again both are totally solid ways to do. Both results should work.

What about in the first summarise?

Unique Values in a List

walkrebe

Hi,

I am trying to get a count of how many unique applications there are for certain groups from the data below

Frequency	Name	Application
Daily	A	Excel, PowerPoint
Daily	A	Excel
Daily	A	Alteryx, Excel
Monthly	A	Excel, Word
Monthly	A	PowerPoint
Daily	B	Excel
Daily	B	Excel

I want to count the number of unique applications grouped by frequency and name e.g like below - any suggestions of how I could do this please?

Frequency	Name	Number of Applications
Daily	A	3
Daily	B	1
Monthly	A	3

Datasets

Accepted answers

All comments

hellyars

@walkrebe

Here is a potential solution.

Screenshot 2021-05-16 131409.png

unique_values_in_a_list_potential_solution.yxzp

hellyars

@walkrebe

I added a Select tool at the end to change Count to Number of Applications.

The first Summary tool combines all the Applications for a given Frequency and Name. Since they are separated by a comma "," I used a Text to Columns set to split to rows using the "," delimiter. A Cleanse tool is necessary to get rid of extra white space, etc. A Unique Tool filters out the duplicate values. The second Summary tool then counts the number of unique applications associated with any Frequency+Name pair.

Screenshot 2021-05-16 131800.png

unique_values_in_a_list_potential_solution.yxzp

apathetichell

Here's the split into rows - summarize/count - data cleanse solution.

count groups.yxmd

apathetichell

group by frequency. group by name. count distinct application.

that's after splitting application and datacleansing so " powerpoint" ="powerpoint"

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703