Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Access SQL conversions

GoldenDesign04
8 - Asteroid

I am in the process of removing all Access DB's from my companies use. For the most part this has entailed:
Connecting to source DB's, reading SQL from Access queries, converting (where needed), lather, rinse and repeat.

 

However sometimes I run across a series where they are equally parts baffling and mind puckering. The one I am running into a problem with now is an excessive use of Make Table to pull small-ish tables, and then join these tables into a larger single table.

 

I am perhaps missing the order of things here, I hope, and could use assistance on how this operations would be done in Alteryx workflow:

 

 

SELECT
   [100_MT].[GN Number],
   [100_MT].[Call Ltr - Band],
   [100_MT].[Station Home Market Name],
   [100_MT].[Total Number of Events],
   RSI.STA_START_DATE AS [START in RSI],
   PPM.START_DATE AS [START in SEMS],
   [103_MT].TRANSITION_DATE AS [Transition Date in SEMS]
FROM
   (
(100_MT
      LEFT JOIN
         PPM 
         ON [100_MT].[GN Number] = PPM.MEDIA_ENTITY_ID) 
      LEFT JOIN
         103_MT 
         ON [100_MT].[GN Number] = [103_MT].MEDIA_ENTITY_ID
   )
   LEFT JOIN
      RSI 
      ON [100_MT].[GN Number] = RSI.ST_STA_GNMBR 
WHERE
   (
(([100_MT].[Total Number of Events]) > 5) 
      AND 
      (
(RSI.STA_END_DATE) Is Null
      )
   )
;

 

 

I've had to anonymize the schema and table names of course so I apologize if it is confusing. Mainly I have connections that include the SQL (already verified outputs) of the different make tables, I am looking for how the series of joins in Alteryx would translate from the above SQL

 

Regards

1 REPLY 1
DanielG
9 - Comet

Not sure exactly what you are asking here, but the code you posted shows you exactly how to connect the tables together and create the query results you'd need to mimic what exists.

 

Based upon the bracketing within the query, set up a Left Join between PPM and 103_MT  union the L&J together then Left Join that to RSI and union the L&J together.  then select all the columns you want from all of those columns you have available after the joins (or even within the joins themselves, if you want to keep the # of tools down).  Finally apply the filter and you should be good to go.

 

If that is it, you can probably get that figured out with a sample being attached.  Because I wasnt sure this is what you were looking for, I didnt build a sample workflow.

 

If that doesnt work try reversing the joins, because maybe I am misreading the brackets...  haha.

 

But hopefully this is good enough to get you what you need.  🙂

Labels