Looping batch of records and updating Dynamic Input tool IN clause

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?

Macros

Dynamic Processing

Accepted answers

jdunkerley79

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

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

All comments

AndyBate

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.

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

regards

Batch.yxzp

jdunkerley79

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

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

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703