This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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
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
#Load Data from Alteryx
pos <- read.Alteryx("#1",mode="data.frame")
lookup <- read.Alteryx("#2",mode="data.frame")
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
where Date > '2013-06-30'
group by TicketId")
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
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?