Select distinct and group by in Alteryx
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sure, i just created a sample dataset.Thank you so much for helping out.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
This is the output of the unique selection:
I hope this helps,
M.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
i will test and get back to you.Thanks a lot.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
