Loop through records - Iterative macro

[Shank]

Dear All,

I am new to Alteryx macro development. Were I have to loop through records and provide them the end result as ok or not ok. Below is the case can some one guide me how can I achieve it. 

Case: I have a set of records grouped at Area level (A & B) and assigned a sequence no (Seq) 

For Each ID's in a area i need to check if my sequence is always in ascending order (Comparing with row -n) then i have to mark it as OK

in below example for ID 952 sequence is 4 but for previous id sequence is 6 which is not in order then i need to mark it as not ok. similarly for ID 3564 sequence is 9 again this is not in sequence because when i compare data with Row - 2 (i.e., 11 which is grater than 9) hence it is not in sequence and marked as not ok 

Sequence.

In this case sequence is dynamic and sequence can be assigned and jumbled at any level. so, I cannot use multirow formula as row -n is not defined.  for example for  A we have (3 sequence assigned)  and for b we have (12 sequence assigned) it can be assigned at 100's as well.

can some one help on this to achieve?

ID's	Area	Seq	Result
101	A	1	ok
201	A	2	ok
301	A	3	ok
401	B	-1	ok
501	B	2	ok
666	B	3	ok
657	B	5	ok
689	B	6	ok
952	B	4	Not-ok
11	B	7	ok
12	B	10	ok
1214	B	11	ok
2569	B	8	Not-ok
3654	B	9	Not-ok
2568	B	12	ok

[atcodedog05]

Hi @Shank 

Can you explain why is Id 11, Seq 7 ok but ID 3654 Seq 9 not ok

ID's	Area	Seq	Result
101	A	1	ok
201	A	2	ok
301	A	3	ok
401	B	-1	ok
501	B	2	ok
666	B	3	ok
657	B	5	ok
689	B	6	ok
952	B	4	Not-ok
11	B	7	ok
12	B	10	ok
1214	B	11	ok
2569	B	8	Not-ok
3654	B	9	Not-ok
2568	B	12	ok

[Shank]

@atcodedog05 

For ID 3654 It is coming after 10 and 11 that is the reason it is not ok.. 

and ID 11 is coming after sequence 6 which is 7 that's the reason it is oK..

[Shank]

@atcodedog05 

Is this achievable?

[atcodedog05]

Hi @Shank 

Maybe, but i was not able to crack a logic yet. I will let you know.

There is lot more amazing people in the community maybe they will able to crack it and provide you solution 🙂

[mbarone]

What is the actual logic?  As @atcodedog05 pointed out, I can't tell what your logic is supposed to be.  They're both in Area B (ID 11 & 3654), yet you look back one row for 3654 to determine it's not okay, and then look back two rows to determine that ID 11 is okay.  I can't quite decipher what you want/need the logic to be.

[Shank]

@atcodedog05  thanks for your prompt response.

Hi @mbarone here is my explanation on the logic required

Sequence is the key here.. Sequence column should always follow smallest to largest. if any where in between sequence number is missed and appeared some where then it has to be marked as not ok..

For A area it has been sequenced in 1-2-3 and all of them are in ascending order

For B area Sequence is different 1 2 3 then 5 appeared instead of 4. so if anywhere 4 appears down the line then it is marked as not ok.. 

here is an example of green is following the sequence but red highlighted are the culprits missed and appeared in between.

ex:- 1,2,3,5,6,4,7,10,11,8,9,12,13,14,16,17,15,18,19,23,20,21,22,24,25

401	B	1	ok
501	B	2	ok
666	B	3	ok
657	B	5	ok	4 is missing as per the seq so next number is 5 hence it is ok
689	B	6	ok	After 5 it is 6 again this is ok
952	B	4	Not-ok	This was suppose to come after 3 hence it is not ok
11	B	7	ok	This was mistake of 4…so seven is in sequence hence it is ok
12	B	10	ok	After 7, 8 and 9  are missing here.. 10 is coming in as per the order so any where in future if 8 and 9 appears it will be out of sequence and it should be marked as not ok
1214	B	11	ok
2569	B	8	Not-ok	this was missed after 7 hence not ok
3654	B	9	Not-ok	as even 8 is missed 9 is also out of sequence hence it is not ok
2568	B	12	ok	8 and 9 are the culprit here hence 12 is as per sequence and it is ok

[mbarone]

That would involve some pretty complex logic, if it can even be done (I can't picture in my head right now how it would be done by anything other than inspection).

However, if it is possible in Alteryx (and this would take a significant amount of time to play with), it would most likely involve the following tool(s):
Record ID

Summarize
Sort

Tile Tool****

*** I think the Tile Tool would be key.

You can read up on it in the Tool Mastery Index pages.

[atcodedog05 · Accepted solution]

Hi @Shank 

Cant believe i cracked it. I understood the pattern basically for each Area each Next Seq should be greater than the current row Seq. If not find the next Seq which is greater than current Seq rest all in between is not-ok. This way it forms a sequence.

Workflow:

1. Using select to deselect result column.

2. Using recordID to set row Id.

3. Using summarize tool to max recordID i,e row count. Using append tool add it back as a column.

4. Designing an iterative macro where the macro is run for each row. n-rows to n-iterations

Macro:

Upper branch

1. I am checking whether iteration number if <= max record ids

Lower branch

1. For each iteration only recordID<iteration number is taken. 

Logic : lets say i am in 4th iteration seq is in order so no action taken. In 5th iteration 5th row is the Seq not in order in this iteration it is removed and also passed to output as not ok flag

2. Using Multi-row formula to check is the current Seq greater than previous Seq if yes flag 0 else 1. If the row is first in the Area (A,B...) then flag 0. This way if last row doesnt not maintain order its flagged

3. Using filter tool filter only flag not-ok. This is sent as output to be mapped.

4. Using join tool i am joining on record id and taking the left un-join. This way the Seq which was wrong is removed. And rest records is passed to next iteration.

Workflow:

1. Map the 'not-ok' rows on record using join tool and mark others as 'ok'

Place both the workflow & macro in folder it should work.

Hope this helps 🙂

[Luke_C]

Hi @Shank 

From what I can gather, we need to track a running max for each area and evaluate if the sequence field is less than that max. I used the multi-row formula tool to come up with the max and then an if statement to check if the record is 'ok' or 'not ok'