Connect in-DB Or Dynamic Input in-DB with Dynamic input tool

[TerryM]

I am trying to query a database using SQL with where statement and load results into another table.

I need to update where clause based on a date range in Connect in-DB or using Dynamic input-in DB. For instance, my date range is June 1st to June 3rd, I use gen_rows to create 3 days (06-01,06-02,06-03) and trying to replace the query in Connect in-DB  or using Dynamic input-in DB with each date. Due to the data volume, I want to process 1 day worth of data and repeat the same thing for all days within date range.

Challenges I am facing are:

1.Connect in-DB tool doesn't allow me to use parameter

2.While trying Dynamic input in-DB, the connection name field is not the connection I specified. It shows VALIDATION_STATUS or CHANGE_METHOD but they are my columns names instead of connection names.

Any suggestions?

[DanielG]

What if you build a macro to pass the data in and just apply the dates via control parameter connected to a filter tool within the macro so that each output is specific to certain date.

[TerryM]

Hi Daniel,

Thank you for the advise but what I am trying to achieve is to utilize in-DB operations. If I build a macro, is the data processed in Alteryx side or DB side. I can get gen rows and dynamic input working which can generate the SQL I need. The challenge is on in-DB tools side.

Does Connect in-DB tool support parameters?

Dynamic input in-DB should show connections name based on Alteryx helps but it doesn't. Anyone experienced the same challenge?

[DanielG]

There is the Macro Input In-DB and Macro Output In-DB tools in the In Database ribbon.

I have never used them, but if you play around you will see that they behave the same way as regular in-db tools and you can only connect them to other in-db tools.  I would assume that means they function within the "database space" and not your "local space".

You will get the macro connectors on tops of the tools to adjust your parameters as needed on a filter/formula/join or pretty much whatever you want to revise.  

Sorry I dont have any sample to send, as I dont know how to dummy up an in-db workflow...  🙂

[yixuankang · Accepted solution]

Hey all,

I just happened to bump into this thread and then looking at the contents of the question posed I also quite coincidentally implemented a solution for my own use case. Hope this is similar to yours, allow me to share.

I am assuming that you wish to generate queries based on each specified date as generated in your generate rows tool. For that yes I used a batch macro and no I did not use a dynamic input in-DB tool.

Here's how it looks from the 'outside':

For me I generated my 'end_date' using a set formula in the generate rows tool and derived my 'start_date', then fed them into the batch macro this way:

In this case I used two batch macro tools in my macro, so there will be two input fields. The values iterate together side by side (i.e. row by row, not a combination of all values). The other two boxes are for my control parameters, they are needed as part of a particular workaround as Macro Input/Output In-DB cannot be used with Batch/Iterative Macros. Remember this well.

Let's explore the macro.

Here's part of the workflow in the macro but it should more than suffice to perform my illustration on how the macro works.

If you notice that interface tool at the top that looks like a gear or something, thats a batch macro tool. Use it to bring in a value from 'outside' that you want to iterate from. So for each batch iteration, the two batch macro tools will bring in the start and end date as specified earlier, and then replace the 'START_DATE' and 'END_DATE' string in my query in the connect in-DB tool. Configure the action tool in this manner:

For each action tool, select the query (just click on it), check the 'Replace a specific string' box and type in the string to be replaced (in my case it is END_DATE, you can specify your own in your query). 

Ok now you have a query that will change the dates dynamically each time, but what about the output? You can't use a Macro Output in-DB!

What I did was to use a holding table to store all my data that comes out for each iteration. For the first run you'd want to overwrite the holding table for a fresh start, then on subsequent iterations append the data into the holding table. To implement this control I used the condition tool (the one with the sun and cloud) to see if the end date from the batch macro tool matches the inputted end date (I configured the end date to run starting from the input end date, so it works here). You can also try using Engine.IterationNumber under the Constants list to do your condition (note that starting iteration number is 0) but somehow I can't make it work for mine.

After that set the action tool for true to "Overwrite" and the action tool for false to "Append". Do it as per the settings below:

Your holding table will eventually hold all the data that you need and technically you can stop the workflow from here (no output tool needed!) But if you don't want to get the data from the holding table then you can also implement yet another role to control when the output happens at the last iteration.

Now to get the last iteration I don't really have a good advice for you on how to make that happen. You can either derive your last iterative date separately if you are using the date interface tool from the outside (sorry I forgot to specify that I created my workflow as an app) or use a summarize tool to get the minimum/maximum date (depending on how you are doing your iteration), append it to all the rows and then stick in yet another batch macro tool for the sole purpose of matching it to your iterating date in the condition tool before enabling the container to do any extra stuff etc.

In my case I did the former by deriving the last start date and matching with the start date in the batch macro tool, once they match (in the condition tool) I know that it is the last iteration and then use the action tool to open up the container (disabled by default) to update my final table etc

Hope this helps in your use case! If not then you still get to learn something 😄

I may have left out some things out of sheer laziness, do ask for any clarifications if you need 🙂

[TerryM]

Thank you so much for sharing. This is exactly what I am looking for.

[garaya]

Hi,

Can you share this workflow without the SQL queries. I´d like to see how the macros work.

Regards,

Gustavo

[gsantos]

Awesome work! Can you share the workflow (without the sensitive informations) so we can have a clearer view of how you managed to work with the macros?

Thank you!