How can I loop through 160,000 records 400+ times using a different filter value each time

[Newt]

I have 160,000+ records, and I need to apply a different Filter value to them 400+ times to get 400+ outputs.

When I tried a batch macro, with the Filter tool tied to a Control Parameter, it just plowed through the 160,000 records once, using the first Filter value that came in through the Control Input anchor.

How can I get it to do it again 399 more times with different Filter values?

When I set up the canvas to be an Iterative Macro, as soon as I put in the Control Parameter, it switches back to Batch.  No dropdown option to return it to iterative.  (I don't know if iterative is actually what I need...)

The image shows the Union where the 160,000+ records are produced.

There are several thousand unique items, and six months in which they were associated with a value (or the value was null).

The Filter selects one of the items, and the Unique comes up with a list where that item showed up in at least one of the months.

The Join matches the list to all the 160,000 records, and the Cross Tab creates the monthly columns, showing how the value for the item varied over the six months or was null.

Now, 399 more times, but how?

There's a concept I'm missing here, obviously!

Thanks for any guidance you can provide.

[jrlindem]

Do some research on "Iterative" Macros.  Macros does feel like the right solution for you, but the iterative nature is more like a traditional loop.  -Jay

[binu_acs]

@Newt use batch macro for dynamic filtering, sample workflow attached

[Yoshiro_Fujimori]

Hi @Newt ,

Here is a very simplified solution with Append Field Tool.
(If your filter is more complicated, you may need to use Dynamic Replace Tool to edit Expression.)

Workflow

Input Data

Key	Value
1	abc
2	bcd
3	def
4	efg
5	fgh

Filter Data

Filter

a

b

c

d

e

f

g

h

1. Append Field Tool

Generates the rows for all the combinations of input data records and filter records.

2. Filter Tool

Filters the Input Data records with each value for filter.

3. Output Data Tool

Outputs a file for each filter.

Result

The sample workflow contains only 5 records in Input Data and 8 records in Filter, which generates 40 records before Filter Tool.
But you can apply the same logic to your data (160,000 x 400+), but it should work in the same way.

I hope this helps.