Alteryx Designer Desktop Discussions

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

Select distinct and group by in Alteryx

Jevish
7 - Meteor

Hello Everyone ,

 

I am having trouble converting the below sql statement into alteryx workflow:

 

select distinct a,b,c,d,e,f,g

from xyz

where f contains "blabla'

groupby a,b,c

 

I am thinking to use summarize tool for group by ,unique tool for distinct and then do a join .The other option is to summarize all the columns in select statement. But iam not sure about either of them.

 

Can someone  please help me out ? waiting for your suggestions and advise.

 

Thank You.

8 REPLIES 8
cplewis90
13 - Pulsar
13 - Pulsar

Hey @Jevish,

 

Can you give a bit more background? A distinct and group by operate in similar fashions, but there is a difference in the way they process the data. 

 

Based on the distinct statement and wanting to recreate in Alteryx tools, you could either copy the query into an input tool, or if you pull more data in your query (not using a distinct or group by), you could then use a summarize tool and group by all your fields listed in your distinct statement. This grabs all the unique combinations of those fields like a distinct would.

mceleavey
17 - Castor
17 - Castor

@Jevish ,

 

As @cplewis90  mentioned, can you provide some mock data showing an example of what you're trying to do so we can help?

 

Thanks,

 

M.



Bulien

Jevish
7 - Meteor

I don't have the data.. The columns data type are text, date and numeric .Just creating alteryx workflow based on the SQL statement . I tried to use the summarize tool but it only gives me the result for columns a,b and c.

mceleavey
17 - Castor
17 - Castor

@Jevish ,

 

Can you mock some data for us?

 

We need a little help!

 

🙂



Bulien

Jevish
7 - Meteor

Sure, i just created a sample dataset.Thank you so much for helping out.

mceleavey
17 - Castor
17 - Castor

Hi @Jevish ,

 

thanks for that.

 

Given you just want to select distinct and given the restriction of SQL, it will simply select the first instances of these combinations.

To do this, use the Unique tool:

mceleavey_0-1623954290986.png

mceleavey_1-1623954310840.png

 

This is the output of the unique selection:

mceleavey_2-1623954339743.png

 

 

I hope this helps,

 

M.

 

 

 

 

 



Bulien

Jevish
7 - Meteor

i will test and get back to you.Thanks a lot.

mceleavey
17 - Castor
17 - Castor

@Jevish ,

 

No problem,

 

Your Where clause is simply a filter tool.

contains([f],"blah")

 

M.



Bulien

Labels
Top Solution Authors