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.

Make Joins Pragmatically

Highlighted
8 - Asteroid
Hi all
I have a need to recreate database functionality within a workflow. I have all the tables stored as yxdb and have all the relationships mapped. What I'm after is a way to programatically to all the joins etc. I have been playing with nested batch macros but am getting stuck.
Any help is appreciated
Highlighted
Alteryx
Alteryx

One option would be to use the R Tool. R has a library called sqldf that allows you to write SQL code against a data frame. Here is an example below

 

 

library(sqldf)

#Load Data from Alteryx
pos <- read.Alteryx("#1",mode="data.frame")
lookup <- read.Alteryx("#2",mode="data.frame")

#SQL Operation
pos_lookup <- sqldf("select TicketID,
                    sum(case when Type='Pizza' or Type='Burger' then 1 else 0 end) as burger_pizza_count,
                    sum(case when Type='Side' then 1 else 0 end) as side_count,
                    sum(case when Type='Drink' then 1 else 0 end) as drink_count
                    from pos t1
                    inner join lookup t2
                    on t1.Desc=t2.Desc
                    where Date > '2013-06-30' 
                    group by TicketId")

 

 

Highlighted
8 - Asteroid
Hi @AndrewKramer
Thanks for the response. Unfortunately my organisation does not like code to be used, hence why we use alteryx. What I would really like is a macro where I can simply input a list of all the joins i want and let it run. I'm sure i could accomplish this using a bit of macro trickery
Highlighted
17 - Castor
17 - Castor

Hi @JTCairns 

 

Why do you need a list of joins?  Why can't you use a series of join tools to replicate the hierarchy of the existing database in your workflows as required?   

 

What format would this list have? Would it be stored in an excel file?  What fields would define a join?  Are you limiting this to simple joins, inner join of Table1 to Table 2 on FieldList,  or are you planning to support the full ANSI set of joins?

 

Dan

Highlighted
8 - Asteroid
Hi @danilang

The simple reason is that there are about 600 joins to do with an average of 30 fields per join. I could sit and do these individually but that seems like a bad option Haha
Highlighted
17 - Castor
17 - Castor

@JTCairns 

 

Where are you getting this list of joins?  From the SQL source?  What preprocessing are you going to do to get it into Macro ready format?  Do you have a macro that parses the SQL text?

 

Dan

Labels