Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamic referencing of fields

gavaksh
6 - Meteoroid

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!

8 REPLIES 8
AngelosPachis
16 - Nebula

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

 

AngelosPachis_0-1618985523869.png

 

Hope that helps,

 

Angelos

gavaksh
6 - Meteoroid

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
16 - Nebula

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
6 - Meteoroid

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
16 - Nebula

And what about the output @gavaksh ?

 

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

 

AngelosPachis_0-1618994849290.png

 

and the other is the selector sheet

 

AngelosPachis_1-1618994865686.png

 

What about your output. How should that look like?

gavaksh
6 - Meteoroid

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:

 

NameField 1Field 2Field 3
Aacbd1234xyz
B1234xyzabc
Cxyzabc1234

 

2) Selector sheet:

 

NameSelector
BField 1
AField 2
CField 3

 

So, the output should be:

 

NameField 1Field 2Field 3New Field
Aacbd1234xyz1234
B1234xyzabc1234
Cxyzabc12341234

 

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

 

Best, Gavaksh

AngelosPachis
16 - Nebula

Hey @gavaksh ,

 

Maybe the following workflow will do it?

 

AngelosPachis_0-1618995919973.png

 

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
6 - Meteoroid

Worked like a charm! Thank you so much!

Labels
Top Solution Authors