Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Using SQL in an Alteryx tool

Highlighted
7 - Meteor

Is there a tool that will allow me to use sql statements to extract and summarize data that is in a .yxdb?

Highlighted
Alteryx Partner

Hello.

If your data is in .yxdb, you dont need sql statements to extract the data, only with input tool and summarize tool you find the answer.

If your data is in DataBase (SQL Server, Oracle, etc.), you can use, for example, the input tool with connection to the DB, or you can use "In DB Tools" of Alteryx to work with the DB by Alteryx (link: https://help.alteryx.com/2018.2/In-DatabaseOverview.htm).

Good Luck!!!

Highlighted
7 - Meteor

Thank you for that reply.  The reason I want to use SQL is that the .yxdb is the detailed, transaction-level data.  A co-worker gave me the SQL code that reads that data and creates summarized report.  I don't want to have to recreate that sql logic using Alteryx formula tools.  It has some CASE statements and other things.

Highlighted
7 - Meteor

Also, the reason I have the detailed, "raw" data in a .yxdb is because the PostgreSQL database that hosts the data is not very responsive.  I will try the in-database tool, but I'm not very optimistic.   

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@buddy_jones wrote:

I don't want to have to recreate that sql logic using Alteryx formula tools.  It has some CASE statements and other things.


I think the response from the Community is that replicating the functionality using Alteryx tools is the way to go. I'm confident the functionality is there, and the Community is definitely here to help! 

 

Do you have any functions/logic that seem like roadblocks? 

Alteryx
Alteryx

If you use the R tool, there is a library you can install called sqldf that you can use to run your sql operations.

 

https://cran.r-project.org/web/packages/sqldf/sqldf.pdf

 

I don't have Alteryx open, but here is a quick example I have from RStudio, in this case a CTE.

raw_cte <- sqldf("with structure (employee, id, man_id,  hierarchy) as (
                  select employee,
                         id,
                         man_id, 
                         1 from raw

                 	union all

                  select t1.employee, 
                         t2.id, 
                         t2.man_id, 
                         t1.hierarchy + 1
                 	from structure t1 join raw t2
                 	on t1.man_id = t2.id)

                  select * 
                  from structure
                 	where man_id is not null
                  order by employee")

 

Highlighted
7 - Meteor

Thank you for that R suggestion.  I'll give it a try (actually, I'll get the person who works for me and who knows R to try it)

 

Buddy

Labels