Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Looping batch of records and updating Dynamic Input tool IN clause

vkarthik21
8 - Asteroid

I have data in the following manner -

 

GroupId,ProductId
1,120
1,123
2,220
2,222

 

I need to use that data above and pass it on to another workflow - Process Products. In this workflow, the input data is a SQL Query and the Product Id values from above go as part of the IN clause. Here is how the query looks like -

 

select productid, name
from products
where productid in (120,123);

 

Here is what I have done so far. I replaced the 'Input Data' with 'Dynamic Input'. I pulled in 'Input Data' tool with column ProductId having 2 row values and connected to 'Dynamic Input' and having the 'Update Where Clause'. This works as a stand-alone. Now I don't know how to go about connecting my original data and looping through whole groups. I pursued through multiple articles on batch macros and iterative macros but it's very confusing and not the same use case that I have. Any help?

3 REPLIES 3
AndyBate
8 - Asteroid

Hi,

 

Unsure if i have understood your query perfectly but thought i would give it go and hopefully get you started in the right direction. If i am completely off track then can you provide a snap shot of your work flow or even a attached example and ill try to get it closer.

 

The workflow uses your example input an feeds it into a batch macro which concat the Product ID into the format of your where query. If you place the dynamic input where the comment box is this should allow you to pull data per grouping. 

Capture.JPG

 

 

 

 

 

 

 

 

Again this might not be perfect but hopefully will get you started.

 

regards

 

 

 

jdunkerley79
ACE Emeritus
ACE Emeritus

I suggest the following:

 

1. Use a select tool to change the ProductID to be a V_String

2. Use a summarise tool to group by GroupID and concatenate the Product ID with , between the values

2018-01-08_09-39-50.jpg

3. Using a dynamic input set the sample query up as:

select (1) as GroupID, productid, name
from products
where productid in (120,123);

4. Switch the dynamic input to Modify SQL query

5. Add a Replace a Specific String replacing (1) with GroupID

6. Add a Replace a Specific String replacing 120,123 with Concat_ProductID (note do not include the brackets)

 

This should then do what you want. Running it will perform a query for each group and get the results

 

An alternative would be to look at the InDB tools and then Data Stream In the list of GroupID and ProductID. You can then join to the products table and then data stream out  the results

vkarthik21
8 - Asteroid

Thanks @jdunkerley79. That works perfectly. I just realized I can concatenate strings using Summarize tool. That's awesome. Thanks again, so for the delayed update

Labels