Iterative Macro

[Shank]

Hi, 

I have a situation where I need to create an iterative or batch macro, and I was wondering if someone could assist me with this.

The scenario is that I have a list of batches, and I need to find the dependent batches for this list of batches (Input 1). For instance, A is the final batch, BB is the ingoing batch for A, CC is the ingoing batch for BB, and DD is the ingoing batch for CC. I need to get the complete link for Batch A, and the input and expected output are as follows:

For me, the in-scope "Type" for the dependent batches are ASM, INS, FLL. (Ref Input 2)

I would appreciate it if you could help me with an iterative or batch macro for this scenario. I tried couple of methods, but it did not work well.

Input 1
ProducedMat	ProducedBatch
1	A
2	B
3	C
4	D
5	E

Input 2
IngoingMat	IngoingBatch	Type	ProducedMat	ProducedBatch
11	BB	ASM	1	A
1236	FSP	FGD	1	A
2697	DPS	FGD	1	A
22	CC	ASM	11	BB
2212	RPS	NGD	11	BB
2213	OPS	MGD	11	BB
33	DD	ASM	22	CC
3313	HPS	URS	22	CC
4414	IPD	KGD	22	CC
44	EE	INS	33	DD
4415	PPS	BRS	33	DD
4416	LPS	MRS	33	DD
55	FF	FLL	44	EE
5515	ZPS	CSP	44	EE
5516	XPS	OSP	44	EE
2456	AAA	ASM	2	A
2487	BBB	INS	2456	AAA
2879	CCC	FLL	2487	BBB

Expected Output
ProducedMat	ProducedBatch	IngoingMat	IngoingBatch	Type
1	A	11	BB	ASM
1	A	22	CC	ASM
1	A	33	DD	ASM
1	A	44	EE	INS
1	A	55	FF	FLL
2	B	2456	AAA	ASM
2	B	2487	BBB	INS
2	B	2879	CCC	FLL

[gawa · Accepted solution]

hi @Shank 

I understand that you want to trace the layer from bottom to top(or top to bottom). In this case, Iterative Macro is the best to pick.

Here is the WF by using Iterative Macro.

To be honest, Iterative Macro is one of the difficult method, and it will take sometime to get used to it.

[Shank]

@gawa Thank you so much for your Help.

Your assistance was invaluable in helping me achieve my goal, and I truly appreciate the time and effort you put in.

The solution you provided worked perfectly fine for me. I am particularly interested in learning more about the iteration macro and would love to get in touch with you to discuss it further. Let me know if it is ok for you :)

[gawa]

@Shank  Glad to hear that!

If you need further discussion, please create another post here in Discussions so that anybody having the same question can refer to the solution in future by making it public. 

At last, I introduce the Weekly Challenge which is to find hierarchy of organization by using Iterative Macro. If you are wondering what you should study first, try this first. Referring to solutions by others will be much help for your further learning. 

https://community.alteryx.com/t5/Weekly-Challenges/Challenge-12-Creating-an-HR-Hierarchy/td-p/36740

Good luck!