Loop Summarize

[zealisk]

Hi all,

New to Alteryx.

I have an excel file that has ~60k unique rows. I want to do a search of their IDs in SQL. However, I am limited to 1000 records inside a WHERE clause.

The wordaround is to do an OR statement so it looks something like this:

WHERE IN ('1','2','3',...,'1000') OR ('1001','1002','1003',...,'2000') OR ('2001','2002','2003',...'3000') etc.

What I have so far is SELECT RECORDS (every 1000) then SUMMARIZE (concatenate)

But then this would mean that I would have to do this 60 times. 

Is there a way that I can have a string variable that updates itself every 1000 record?

Kinda like:

1st iteration

str =  1','2','3',...,'1000'

str = "(" & str & ")"

2nd iteration

new_str = '1001','1002','1003',...,'2000'

new_str = "(" & new_str & ")"

str = str & " OR "  & new_str

and so on until last row

[JosephSerpis]

Hi @zealisk if you use a dynamic select tool you can have where clause they will dynamically update for all the iteration you need. You would need to ensure you had some logic that fed the iterations to update the where clause within the Dynamic Input tool. The Multi-Row formula tool would be useful to create you iterations of strings that have a 1000 or less records. 

[JosephSerpis · Accepted solution]

Hi @zealisk I mocked up a workflow that showcase an approach using the Multi-Row Formula tool to create group/iterations based on a row number.

[zealisk]

What an elegant solution!

Thank you very much