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.
Solved! Go to Solution.
@MarqueeCrew
Thanks for prompt response, maybe since I am new to this tool so could not follow your suggestion.
I have the following data
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 |
a | b | g | j | a | a | a | a | a |
a | b | g | j | a | a | a | a | a |
a | b | g | j | 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
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
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
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.