Dynamic referencing of fields

[gavaksh]

Hi community,

I am pretty new to Alteryx and so apologies in advance if the my question is fairly easy or has been referenced in other threads.

I have a data set, a fairly abridged version of which is as below:

Name     Field#1     Field#2    Field#3      Field#4

A              null           1234         null          Field#2

B             1234          null           null          Filed#1

C              null           null          1234         Filed#3

To summaries, columns Field#1, Field#2, Field#3 are the ones that have actual data and there is a Field#4 that has been added to point which filed has the data. The reason as to why I have to add Field#4 is because the data stream owner wants the column "selector" to be outside Alteryx (and sitting in an excel file). With that thought, how can I convert the text 'Field#2' in Field#4 to its corresponding value without reapplying the column selector logic again?  

Thanks in advance for looking into this!

[AngelosPachis]

Hey @gavaksh ,

You can achieve this by transposing your data in columns Field1,2,3 on a vertical axis and the using a Join tool, joining on both Name(A,B,C)  and the transposed column headers

Hope that helps,

Angelos

[gavaksh]

Thanks @AngelosPachis for your thought and taking the time to create a workflow for me. But unfortunately this solution may-not work in my case as the column selector ie. Field#4 a evaluates a number of parameters in an excel based on which it spits out the filed name to be selected. For operational reasons that has to remain outside Alteryx.

So in essence, I need a way to tabulate the value only by looking at Field#4 that has the name of the Field from where the data needs to be picked up.

[AngelosPachis]

Hi @gavaksh ,

Can you please share with us the input that you think should be fed into Alteryx and the desired output, cause I'm currently a bit confused with what you can/can't use.

From your last post, it appears that Field 4 is not currently in your data set and you want to create it by looking at fields1-3?

Thanks,

Angelos

[gavaksh]

Hello,

The workflow will have two input streams: 

1. An excel sheet that has 31 columns and 15K plus rows. For each individual line item some of the the 31 columns will have some data.
2. Second sheet is a 'selector sheet' that selects one of these 31 fields based on certain logic. The data here would name one of the 31 columns. This logic, as I previously mentioned, needs to stay in excel.

On both these input streams, I do a join to come up with Field#4 as referenced in the original post. Hope this provides additional context to the question.       

[AngelosPachis]

And what about the output @gavaksh ?

So you will have two inputs, one is the main data

and the other is the selector sheet

What about your output. How should that look like?

[gavaksh]

Hello!

Output should be the fields in the main data and a new filed. The new fields should have the data from the corresponding field mentioned in the Selector sheet.

So if the inputs are:

1) Input sheet:

Name	Field 1	Field 2	Field 3
A	acbd	1234	xyz
B	1234	xyz	abc
C	xyz	abc	1234

2) Selector sheet:

Name	Selector
B	Field 1
A	Field 2
C	Field 3

So, the output should be:

Name	Field 1	Field 2	Field 3	New Field
A	acbd	1234	xyz	1234
B	1234	xyz	abc	1234
C	xyz	abc	1234	1234

Thanks you so much for taking the time to help me.

Best, Gavaksh

[AngelosPachis · Accepted solution]

Hey @gavaksh ,

Maybe the following workflow will do it?

Same logic as before, but with an extra join since we want to have to separate input streams.

Let me know if that works for you.

Cheers,

Angelos

[gavaksh]

Worked like a charm! Thank you so much!