Using SQL in an Alteryx tool

[buddy_jones]

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

[atrozz]

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!!!

[buddy_jones]

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.

[buddy_jones]

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.   

[CharlieS]

---

@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? 

[AndrewKramer]

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")

[buddy_jones]

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