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

Loop through Dynamic Input with In

joejoe317
8 - Asteroid

Hello everyone, I have been trying to work on this for a while now (am new to alteryx) and am at the point where I just want to bust out some python and do it that way. 

 

Issue is that we use Oracle, so the in clause limits to 1000. I am just testing, but in the current dynamic data I pulled I have around 2700 records. 

 

What I have done so far is broken down a count of 0,1,2 in a list.

 

What my thought was is that on the first iteration (0) I could pass the first 1000, then on iteration (1) I could pass the second 1000, then on interation(2) pass the rest. 

 

I have a dynamic sql going as well. So What  I was doing is grabbing the first random Id's and passing those in the in clause using dynamic input. 

 

Could someone please point me in the correct direction?

 

So far I am trying to do a batch macro, but am kind of lost in terms of adding a custom loop count, then also passing the dynamic data into the dynamic input for that iteration.

 

I should also mention I am quite new to SQL as well, but found a post on here about this:

OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;

The SQL running is about 400+ lines.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Run-a-query-1000-records-at-a-time/td-...

8 REPLIES 8
CharlieS
17 - Castor
17 - Castor

There's an easy (non-batch) work around for this. It relies on the feature that the Dynamic Input tool batches and unions all the results together. So rather than inputting 1 record with 2700 conditions into the Dynamic Input (which doesn't work), make 3 rows with <=1000 conditions in each and the Dynamic Input tool will output them all together.

 

I've attached an example solution I built last week to show how to use a RecordID and Formula tool to achieve this.

joejoe317
8 - Asteroid

I got it to at least start processing. I have gone through your workflow many times to see the data types and see that they are all wrapped in quotes at the end. My data types are literally the same as yours, but cannot get them to do this. At thigs point they are just showing as example:

1234,1235,153,654363,65436435

 

Does this ring any bells?

joejoe317
8 - Asteroid

nevermind I found the concat options at the bottom of the sum block. 

joejoe317
8 - Asteroid

Okay, so now it processed, but is not working. I have successfully gotten this to work with < 1000 as an w_vstring

 

Here is my replace in dynamic input Capture.PNG

joejoe317
8 - Asteroid

I got it working! Thank you so much.

 

For whoever else uses this, the issue was the "Update WHERE Clause"

I deleted that rule, and changed it to replace string 

 

I changed

'placeholder'

to Replacement_Field

joejoe317
8 - Asteroid

With this method, are we doing queries multiple times, vs one query and looking at the resuls?

CharlieS
17 - Castor
17 - Castor

I'm glad you were able to get things working!

 


@joejoe317 wrote:

With this method, are we doing queries multiple times, vs one query and looking at the resuls?


If you're using the solution I posted to create multiple records, each record going into the Dynamic Input is a query that is executed. The Dynamic Input will execute those queries one by one, then the results from all queries are union-ed together before they are output from the tool. This process should be indicated by the Results window that outlines each query executed.

joejoe317
8 - Asteroid

Thats what I thought, Thanks Charlie.. You have been helpful today. 

Labels