Data Sequencing based on Current & Previous ID Fields

Hi all,

I have become stuck trying to generate a solution to the below problem. I have tried to pivot the data, and have attempted to start building a macro, but have had no luck.

I have data which has the following fields:

Record ID (integer) (set to phonetic alphabet to avoid too many numbers)
Current Task ID (hex code)
Previous Task ID (also hex code)

I have shown an example of the expected input and output below.

Essentially, I am trying to group the data (as per the output for the dummy problem below) by finding matches between Current and Previous Task IDs (Previous is Null if this is the first task).

I'm stuck as there are no other identifying fields to use, and I'm not sure how to set up an iterative macro in this case. It will need to be dynamic, as the number of items per group will change. The below example in/out might help (hopefully). Happy to answer any questions, and thanks in advance!

Example Input:

Record ID	Current ID	Previous ID
Alpha	1	[Null]
Bravo	2	1
Charlie	3	2
Delta	4	3
Echo	A	[Null]
Foxtrot	B	A
Golf	C	B
Hotel	X	[Null]

Example Output (I have tried colour-coding to make this clearer):

Group	Item	ID
1	1st	1
1	2nd	2
1	3rd	3
1	4th	4
2	1st	A
2	2nd	B
2	3rd	C
3	1st	X

Developer

Macros

Dynamic Processing

Data Investigation

Accepted answers

CoG

If the order is not correct to begin with, then here is a macro and workflow structure that will still get the desired output:

Hope this helps and Happy Solving!

Sample Workflow.yxzp

All comments

ChrisTX

Try the Multi-Row Formula tool. Does the attached workflow look OK?

Screenshot 2024-08-20 073301.png

Chris

Data Sequencing based on Current & Previous ID Fields.yxmd

CoG

If the order is not correct to begin with, then here is a macro and workflow structure that will still get the desired output:

Hope this helps and Happy Solving!

Sample Workflow.yxzp

peterr_h

@ChrisTX thanks for the suggestion - I should've added that the order is jumbled and the previous/current task IDs are not concurrent so this isn't a solution that'll work I'm afraid. But thank you!

peterr_h

@CoG thank you so much - this is exactly it!

I do have one question - I thought that iterative macros required some True/False logic to be met to re-feed data through. I can see that this macro works, but I can't quite tell why it does? I know that's a vague question, but I'm hoping to understand how this works without any T/F logic. Does it just run until the I output no longer has any constituent parts? How does this work when there are items which do not join?

Sorry!

CoG

Iterative macros are very powerful tools in your arsenal when properly understood. The True/False setup is a common limitation artificially imposed upon Iterative macros. You can build an iterative macro in that way, but they can do much more).

In the broadest sense, an iterative macro is defined by 3 things:

Iteration Input
Iteration Output
The Workflow in-between

They work by first running the workflow (#3) for your starting data (what you connect to the input anchor/(s) of the macro) just like a standard macro, and then everything that is output to the Iteration Output (#2) gets sent back through the Iteration Input (#1) to start the process over again. This is done until the Iteration Output receives no data (i.e. 0 records) or your macro reaches the maximum number of iterations (that you've defined). Finally, output from each iteration are Union-ed together to provide the dataset in each of your defined output anchors

That's it. You can generate other outputs based on your requirements, but as long as those 3 things are implemented, your macro will work. The tricky part is coming up with the most efficient way to step through your data in an iterative way so that you achieve the empty Iteration Output before the workflow iteration maximum while also tagging relevant information for your other outputs (like the Group # in your case). This allows the following structures to be implemented:

True/False logic you mentioned
Map Traversal (basically a Breadth First Search algorithms): This is what I used for your particular use case. Your Iteration Input/Output are a single "time step" and every iteration references a secondary "Map" input, which is static (i.e. does not change) and contains all information about how records are interconnected to determine what the next value should be.
On/Off logic: similar to True/False except instead of referencing each record individually, you perform a secondary operation and either send all records to Iteration Output or no records to Iteration Output depending on the outcome of the secondary operation (The times I have used this model, usually reference a Join Tool Output, Count Records, Append to main dataset, Filter all records if [Count] > 0 (Just remember to remove the [Count] column via Select Tool right before the Iteration Output)
etc.

There are many other forms and use cases for the iterative macro, and often the planning stage takes more effort than the building of the workflow. In your case the iterative macro is initialized with all Task ID's that have no associated prior tasks (adding Group and Item fields for tracking purposes). Additionally, the "map" table with all Task associations is passed in for static referencing (I filtered out the starting tasks, but did not need to).

Our Iteration Input thus contains a list of unrelated independent task ID's, and our "iteration" goal is to gather the next associated task ID's, which can be found by joining to the "map" table. Everything that doesn't join in a single iteration is irrelevant in that iteration (because it was either already handled or will be later on). The only exception would be a task record with a bad reference (a record with a prior Task ID that does not exist in the dataset).

Hope this helps and Happy Solving!

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335