Loop through Dynamic Input with In

joejoe317

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-p/99663 

CharlieS

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.

20180831-BatchQueryInputs.yxmd