Dynamic Input IN-DB

Hi everyone, looking for some help.

I want to dynamic update my SQL Where Clause. However, there is a limitation on the qty of records you can have in the Where clause (around 16k). I have a list with more than 100k records,

I am looking for a solution where I can break this in batches of 15k and dynamic send them to the Dynamic Input IN-DB for the update of the where clause. I am not sure how can I make the Dynamic Input IN-DB run in batches.

Thank you so much,

Gabriel

Dynamic Processing

In Database

In-Database

Accepted answers

Robin_McIntosh

@gcosta - make sure your macro is set to a Batch Macro. The default is Standard.

And make sure your mapping the correct fields to your input.

All comments

alexnajm

@gcosta I was going to recommend a batch macro, but I don't think this is doable In-DB - would this option work? In-DB Batch Macro - Alteryx Community

Robin_McIntosh

@gcosta Could you do something like this where you create groups of your data and then pass each group into a Dynamic Input In-DB?

You would change the formula denominator to 15K or whatever batch size you need. This will then create groups or batches of 15K records.

OllieClarke

@gcosta

This might not be the most efficient, but what if you used a Join as a where clause? Then you can read in all 100k values and get your output where they match?

Ollie

gcosta

Hi @Robin_McIntosh, thanks for replying. Not really because of the way that the Dynamic IN DB tool works. You can connect data like this. At least not in this way

gcosta

Hey @alexnajm , thanks for the help. I ve tried. Did several iterations trying to get a batch macro working but doesn´t look like I can do that on the Dnamic Input IN-DB tool. I was wondering if perhaps I just use the standard Dynamic Input tool and see if I can make the changes and run the queries several times.

gcosta

Hi @OllieClarke, thanks for the help. I am not sure I am following you. Could you please share an example of what you mentioning?

Thank you so much!

Robin_McIntosh

@gcosta An updated example where in the In-DB part is in a Batch Macro. You feed over the connection file and query based on group (batch) and then all group results are then combined back in the workflow.

In my example, I'm summarizing (concatenating) the values to be queried based on each group.

Then use the formula tool to build each batch query and also bring in the In-DB Connection File.

And then within the Batch Macro, the In-DB query will run for each bacth or group.

gcosta

Hi @Robin_McIntosh . Thank you so much for the example. I think this can do the job.

Can you share the configuration of the macro? I am trying to set this right but I am getting the follow error:

Thank you!!

Robin_McIntosh

@gcosta - make sure your macro is set to a Batch Macro. The default is Standard.

And make sure your mapping the correct fields to your input.

apathetichell

What database are you using - I run an IN statement (effectively a Where clause) with close to a million options on Redshift.

short is - I'd look into using an IN clause vs a Where clause if you are looking at values in the same field. If you want me to walk you through this - feel free to DM me.

gcosta

Yes!!!! It worked!

Thank you so much @Robin_McIntosh . Such a nice workflow. It opens up so many solutions. Love it. I appreciate it your help!!

Robin_McIntosh

@gcosta - Happy to help and glad it worked out for you.

apekshitdhoke

@Robin_McIntosh - In the formula tool, please advise you just defined the path where "In-DB Connection" file is saved on your system? How did you save the In-DB Connection file to your system. I understood the rest but not able to figure out this part.

apathetichell

@apekshitdhokeyou must configure an in-db connection under advanced options/manage in-db.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703