Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Find unique values in each field

akumarbvp
5 - Atom

I am new to Alteryx, and would need to find  unique values in each column within a spreadsheet. I can do a individual unique over each column but my spreadsheet has 70 odd columns and was wondering if there is way to iterate through columns and do unique over each column. 

 

Please also suggest if there is any other suitable tool to achieve this.

5 REPLIES 5
MarqueeCrew
20 - Arcturus
20 - Arcturus
If you transpose the data first, you will get name value pairs. Only select the fields that your interested in when you select the transpose tool. Then you’ll use the unique tool an select both the name and value fields. Your output will be a list of your unique values.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
akumarbvp
5 - Atom

@MarqueeCrew 

 

Thanks for prompt response, maybe since I am new to this tool so could not follow your suggestion.

 

I have the following data

 

Field1Field2Field3Field4Field5Field6Field7Field8Field9
 a  a  a  a  a  a  a  a  a 
bbbbbbbbb
   g   g   g   g   g   g   g   g   g
   j       j       j       j       j       j       j       j       j    
 a bgj a  a  a  a  a 
 a bgj a  a  a  a  a 
 a bgj a  a  a  a  a 

 

 

and what I intend to achieve is  following . Spreadsheet I am using has 200k rows.

 

Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field9
a a a a a a a a a
b b b b b b b b b
g g g g g g g g g
j j j j j j j j j

 

I tried your suggestion with following workflow 

 

image.png

and I am getting following result , maybe I did not follow you.

 

Field1 a
Field1 b
Field1 g
Field1 j
Field2 a
Field2 b
Field2 g
Field2 j
Field3 a
Field3 b
Field3 g
Field3 j
Field4 a
Field4 b
Field4 g
Field4 j
Field5 a
Field5 b
Field5 g
Field5 j
Field6 a
Field6 b
Field6 g
Field6 j
Field7 a
Field7 b
Field7 g
Field7 j
Field8 a
Field8 b
Field8 g
Field8 j
Field9 a
Field9 b
Field9 g
Field9 j

 

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus
To convert back to rows:

Cross Tab with name as header and value as value.

I think that it will produce what you’re asking for? In what was produced originally it is a list of unique values by field. If you use a key, then you can produce unique values by that key.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
MatanGee
5 - Atom

Hi

Thanks for the help, but becuase i have diffrent number of items in each coulmn i get a lot of empty cells after using cross tab.

 

is there anyway around it.

 

to pick up from the example above:

 

col1 a

col2 1

col3 dad

col1 b

col2 2

col 1 c

 

thanks

 

into

col1  col2  col3  

a       1      dad

b       2      

c

nishanttayal
6 - Meteoroid

I was also encountering many blanks and eventually identified the better solution to get this result :

 

Step1: After transpose and removing the duplicates add formula tool to add dummy integer column with value 1.

 

Step2: Calculate the running total of dummy column grouping by Name column of transpose result.

 

Step3: Now use Crosstab and group data by Running total output. 

 

Here you go.

 

clipboard_image_0.png

Labels