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 XCol Yf1f2q1q2Col ACol BCol CCol DJM1 f1 JM2 f1 JM3 f1 JN 14 f2 JN 15 f2 JN 16 f2 KO 37 q1 KO 38 q1 KO 39 q1 KP 50 q2KP 51 q2KP 52 q2 To: Col XCol Yf1f2q1q2Col ACol BCol CCol DJM1 1 JM2 2 JM3 3 JN 14 14 JN 15 15 JN 16 16 KO 37 37 KO 38 38 KO 39 39 KP 50 50KP 51 51KP 52 52 Can you please help me solve this.. Due to my organization restrictions, I cannot upload anything. Thanks, Krishna.

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

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.

Macros

Transformation

Accepted answers

TonyA

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.

Replace.yxmd

All comments

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.

LookUpNames.yxmd

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

Col A

Col B

Col C

Col D

Col E

Col F

Col G

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

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.

Replace.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703