Lookup data in another column based on column name in one column

[chaitu038]

Hi,

   I'm new to Alteryx and trying to find solutions to a problem that's seemingly easy to do in Python.

  My table has a set of columns with very generic names with values and another set with meaningful names with generic column name references. The columns with meaningful names are supposed to replace the ones with generic names.

Example - 

From:

Col X	Col Y	f1	f2	q1	q2	Col A	Col B	Col C	Col D
J	M	1				f1
J	M	2				f1
J	M	3				f1
J	N		14				f2
J	N		15				f2
J	N		16				f2
K	O			37				q1
K	O			38				q1
K	O			39				q1
K	P				50				q2
K	P				51				q2
K	P				52				q2

To:

Col X	Col Y	f1	f2	q1	q2	Col A	Col B	Col C	Col D
J	M	1				1
J	M	2				2
J	M	3				3
J	N		14				14
J	N		15				15
J	N		16				16
K	O			37				37
K	O			38				38
K	O			39				39
K	P				50				50
K	P				51				51
K	P				52				52

Can you please help me solve this..

Due to my organization restrictions, I cannot upload anything.

Thanks,

Krishna.

[JoeS]

Hi @chaitu038 

Not sure if I have missed this slightly.

But I have built a process whereby you remove the fields on the top stream that contain the references.

Select those same columns in the bottom stream within the transpose tool a data columns.

You can then dynamically rename the original columns to their useful name.

[AV8]

Hi @JoeS

Thank you for the solution. Though it was not my query but I was interested in its solution.

Am not sure if this solves @chaitu038 's problem. But I was just curious to know how will we handle it if columns containing references(i.e. Col A, Col B etc) contain references to multiple columns. For example if Col A contains reference to f1 in first row and f2 in second row? 

[chaitu038]

Thanks @JoeS for the solution and @AV8 for adding on.

@JoeS, sorry I simplified the problem too much. The named columns and unnamed columns don't actually comply.

Consider this - 

From:

Col X

Col Y

f1

f2

q1

q2

Col A

Col B

Col C

Col D

Col E

Col F

Col G

J

M

1

31

f1

q1

J

M

2

32

f1

q1

J

M

3

33

f1

q1

J

N

14

f2

f2

J

N

15

f2

f2

J

N

16

f2

f2

K

O

7

37

47

q1

f1

q2

K

O

8

38

48

q1

f1

q2

K

O

9

39

49

q1

f1

q2

K

P

50

q2

K

P

51

q2

K

P

52

q2

To:

Col X	Col Y	f1	f2	q1	q2	Col A	Col B	Col C	Col D	Col E	Col F	Col G
J	M	1		31		1				31
J	M	2		32		2				32
J	M	3		33		3				33
J	N		14				14				14
J	N		15				15				15
J	N		16				16				16
K	O	7		37	47			37		7		47
K	O	8		38	48			38		8		48
K	O	9		39	49			39		9		49
K	P				50				50
K	P				51				51
K	P				52				52

Simply put - Alteryx has to lookup the column reference under named columns (Col A, B, etc) and get the corresponding value from the referenced column for that row.

I used Python script to do this, but it's not straightforward and due to the volume of data I'm dealing with (26GB file), I had to configure batch macro to process a set of records each time. I'm curious to see if this can be done natively in Alteryx.

Thanks for the help,

Krishna.

[TonyA · Accepted solution]

I think this will do it. Let me know if I missed something.

EDIT: I added some notes to the workflow and changed the output data type to double to match the original data.

[chaitu038]

Awesome! Thanks @TonyA for the solution. I had to make only minor changes for my data, but could implement the solution as is for my process.

Alteryx processed massive dataset with 9M+ rows & 100 cols like a champ. The pivot step made the data to almost 1.3B rows and 385GB, but it had no problem running the workflow on my 32GB RAM. Just mind blowing.

Thanks again for the solution.

Krishna.