I need a vlookup macro in Alteryx to solve an iteration lookup issue

[Suppu]

Hello,

I need to keep iterating and lookup values in the same two columns unless I get and N/A . How do I do it ?
In the below example I kept on with lookups. If the result of vlookup is 0 then stop  or N/A. Please help.

Row Labels	SUM	VLKUP1	VLKUP2	VLKUP3	STOP
0		#N/A	#N/A	#N/A	#N/A
152		0	#N/A	#N/A	#N/A
981		0	#N/A	#N/A	#N/A
982	0.5	0	#N/A	#N/A	#N/A
9823	0.25	0	#N/A	#N/A	#N/A
32943	10	4433	152	0	#N/A
32945	11	152	0	#N/A	#N/A
4433		152	0	#N/A	#N/A
(blank)		#N/A	#N/A	#N/A	#N/A

After that I need to collate all the values in the above sample for 152 to get all the value in SUM column that corrspond.

32943 is related to 4433. Next iteration 4433 is related to 152. Similarly we get 152 for 32945. So we we add them to get SUM for 152.

While other values such as 981,982 remain as is with no iterations as they hit 0 in first round.
Please help .

[caltang]

It's very confusing to understand what you're asking... can you simplify the question and requirements?

Based on your data, I would say it would be good to Transpose your columns into rows by grouping them with a RecordID and Row Labels. You can use a few tools to get what you what, like a Multi-Row tool etc. without the need of iterative macro in this case (I believe, based on the information provided).

[Suppu]

Hello,

Below is a simplified version of my request.  I simply want to get total values for D . This is based on raw data I make a pivot table and then lookup values from the raw data to get related values . Please suggest.

Post Pivot Lookup Table :

Row Labels	SUM	VLKUP1	VLKUP2	VLKUP3	STOP
0		#N/A	#N/A	#N/A	#N/A
D		0	#N/A	#N/A	#N/A
E		0	#N/A	#N/A	#N/A
F	0.5	0	#N/A	#N/A	#N/A
G	0.25	0	#N/A	#N/A	#N/A
A	10	B	D	0	#N/A
C	11	D	0	#N/A	#N/A
B		D	0	#N/A	#N/A
(blank)		#N/A	#N/A	#N/A	#N/A

Sample Raw Data:

CHILD	PARENT	SUM
I	A	2
I	A	2
I	A	1
K	E	0.5
K	F	0.25
G	C	3
G	C	2
G	C	2
G	C	2
G	C	1
G	C	2
G	C	1

[AndrewDMerrill]

This is still somewhat unclear. The issues that still aren't clear for me:

1. Why does your Sample Raw Data has multiple duplicated child parent records with different [SUM]'s?

2. What is your vLookup on? Your Sample Raw Data has nothing in common with your Pivot Lookup Table? Would you post what the Pivot Lookup Table would look like if only using the Sample Raw Data that you included?

3. How is [SUM] calculated in the Pivot Lookup Table?

For this kind of problem it would be tremendously helpful to have Sample Input (like the raw data you included already), the Expected Output (for that Sample Input), and maybe even a step by step description for how one of the rows in the output was calculated.

The Iterative Lookup bit sounds to me like a variation of this weekly challenge: https://community.alteryx.com/t5/Weekly-Challenge/Challenge-12-Creating-an-HR-Hierarchy/td-p/36740

[Suppu]

Hi @AndrewDMerrill ,All

The post data has been made using excel but I want to use Alteryx and automate

I am doing a pivot on the raw data . So duplicates in parent child are summed. Sample after just Pivot :

Row Labels	SUM
0
D
E
F	0.5
G	0.25
A	10
C	11
B

Next the Row labels marked  I do a vlookup and continue to do until I get an N/A as there can be mutiple child-parent even after the pivot. After 1st iteration I get :

VLKUP1
#N/A
0
0
0
0
B
D
D

Next iteration and so on :

VLKUP2

#N/A

#N/A

#N/A

#N/A

#N/A

D

0

0

I need to write a macro of some sort so that it keeps iterating until it gets a 0 or N/A . If we hit N/A the macro stops for that row.
Imagine the raw data having mutiple hierarchies within the 2 columns child and parent and I need to calculate the overall total sum for a child parent . In the example value D which we get by A->B->D. After I know all the underlying sub hierarchies I will add the corresponding values to get the SUM. Please revert with questions.
In my previous post see the tables I want to create the vlookup columns via Alteryx 

CHILD	PARENT	SUM
I	A	2
I	A	2
I	A	1
K	E	0.5
K	F	0.25
G	C	3
G	C	2
G	C	2
G	C	2
G	C	1
G	C	2
G	C	1

[AndrewDMerrill]

I understand the iterative process, but I still don't understand where the relationships are coming from.

You said that duplicates in parent-child are summed, this implies that using the Sample Raw Data you included above, step 1 (removing duplicates through aggregation) would result in:

Child	Parent	Sum
I	A	5
K	E	0.5
K	F	0.25
G	C	13

^Is this correct?

Step 2 (Generate Row Labels where [Sum] is allocated to Parent):

Row Label	Sum
A	5
E	0.5
F	0.25
C	13
I
K
G

^Is this correct?

Step 3 (Perform Iterative Lookups from Parent to Child):

Row Label	Sum	VLKUP1	STOP
A	5	I	n/a
E	0.5	K	n/a
F	0.25	K	n/a
C	13	G	n/a
I		n/a	n/a
K		n/a	n/a
G		n/a	n/a

Finally, you mention performing some kind of summation based on the result of VLKUP columns, but I don't understand what that's supposed to look like. If all of this is what you're looking for, please add the result of that final summation step. Otherwise, please correct. What I have included above would be a very helpful form of clarification from you so that you can get the help that you are looking for. Please be a specific and simple as you can because we don't have the data or understanding of the problem that you do. I don't to steer you in the wrong direction because I misunderstood what it was that you are trying to accomplish.

[Suppu]

Hello,

I am trying with a mock up data . Below is a raw data :

CHILD	PARENT	SUM
I	A	2
I	A	2
I	A	1
K	E	0.5
K	F	0.25
A	D	3
A	D	2
A	D	2
A	D	2
A	D	1
E	G	2
G	C	1

This is after pivot :
Row Labels        Sum of SUM

A	5
C	1
D	10
E	0.5
F	0.25
G	2

After first round of vlookup :

Iteration 1

D

#N/A

#N/A

G

#N/A

C

This is after second round of iteration :

Iteration 2

#N/A

#N/A

#N/A

#N/A

#N/A

#N/A

Since we hit N/A we stop. Meanwhile Iteration 1 Sum values are added to the earlier parent to get total value.

The requirement of mutiple vlookup is that parent can be a child of some other value . So it becomes the new parent and the sum values are transferred are added to the new parent and so on unless there is nothing to lookup to.

[AndrewDMerrill]

In the data you provided above, you have the case where a child "K" has multiple parents "E" & "F". How would this be handled since vLookup typically only returns single value?

everything else is very helpful, and I think it has cleared up a lot for me! Thank you.

[Suppu]

Hello @AndrewDMerrill ,

Good catch !! In actual data there will not be any scenario like that . It will mapped to one value.
Please help me .

Edited the table :

CHILD	PARENT	SUM
I	A	2
I	A	2
I	A	1
K	M	0.5
M	O	0.25
A	D	3
A	D	2
A	D	2
A	D	2
A	D	1
O	G	2
G	C	1

[AndrewDMerrill]

This needs to be check, but its a least a start. Here is a sample based on my understanding of the problem statement. Please verify thoroughly as I'm not sure if I performed the sum correctly:

Main Workflow:

Iterative Macro (vLookups):