Alteryx Designer Discussions

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

Dynamic Multi Field Value Replacement

mjh418
8 - Asteroid

Hi All !

 

I need help with dynamic multi-field situation. 

Situation is below:

 

1. The number of Fields which differ by number suffix(1,2,3..) is NOT FIXED. So sometimes, there can be just two fields, Type and Type2. But sometimes it can be Type ~ Type 10 with 10 columns. 

2. I need to build a dynamic formula which IF TYPE is blank, then use the Type2 OR WHATEVER COLUMN THAT HAS A VALUE.

At least the good thing is there will always BE JUST ONE column that has A VALUE.  There will not be two or more columns with values. IF one column has a value, then REST columns will ALWAYS be empty. Just that the number of columns are not fixed but changeable.

 

TypeType2Type3TypeXX....
11   
 22  
  33 
   46

 

Expected Outcome:

Type
11
22
33
46

 

Thank you so much for your time.

10 REPLIES 10
atcodedog05
21 - Polaris

Hi @mjh418 

 

Best way to solve this to use transpose tool. It will work n number of type columns when they are set as values.

 

Workflow:

atcodedog05_0-1626849059417.png

 

1. Using transpose to convert columns to rows.

2. Using filter tool to keep only non nulls.

3. Renaming and selecting required columns.

 

Hope this helps : )

mjh418
8 - Asteroid

@atcodedog05 

Thank you for your response. 

But this doesn't seem to work. 

Sorry but there are actually other columns besides these that need to be retained.

Also, the Type1 actually is just Type without number.

The values that exist are in random locations not necessarily going from left to right. 

 

 

Qiu
18 - Pollux

@mjh418 
I believe a sample data set for input and output is necessary here.

atcodedog05
21 - Polaris

Hi @mjh418 

 

All the other column which is not included in the calculation can be marked as key columns like below.

 

atcodedog05_0-1626850056063.png

 

Output:

atcodedog05_1-1626850097854.png

 

If you provide a sample file we can help you out accordingly.

 

Hope this helps : )

mjh418
8 - Asteroid

@atcodedog05 

Thank you. I think it kind of works. 

I have 2 follow up questions.

1. In the workflow, I would have checked for Type 1 ... to Type 4 for example. If the source data actually has until Type 11.  Then ALL the type~type11 will automatically be CHECKED ON ? (It won't only check for Type 1 until 4)

2. If I actually have 3 different categories of TYPE, For example, TYPE1~3, then LEVEL1~3, CLASS1~3, how do I put these different categories back as columns after this? (not under one column but 3 columns)

atcodedog05
21 - Polaris

Hi @mjh418 

 

1st question: 

You would need to make sure all other columns which doesn't need to be used in calculation are selected as keys. That all other fields will be included in the calculation.

 

2nd question:

a. You can handle them separately using 3 different transpose tools.

b. You can use a transpose tool - crosstab tool combo to apply and create multiple columns.

 

If you provide a sample file we can help you out much better.

 

Hope this helps : )

mjh418
8 - Asteroid

@atcodedog05 

Thank you so much for your help by the way. 

 

1. So you mean that as long as I designate the FIXED columns as KEYS, all the rest of them (dynamically) will be selected automatically as data columns no matter how many?

 

2. Okay so after applying your help, now it looks like this:

 

FIXED COLUMN1FIXED COLUMN2NameValue
xxxxA_Type111
xxxxB_Type1555
xxxxC_Type13333
xxxxA_Type222
xxxxB_Type2666
xxxxC_Type24444
xxxx

A_Type3 and so on....

33

 

now I need it to be:

FIXED COLUMN1FIXED COLUMN2A_TypeB_TypeC_Type
xxxxCorresponding ValuesCorresponding ValuesCorresponding Values
xxxxCorresponding ValuesCorresponding ValuesCorresponding Values
xxxxCorresponding ValuesCorresponding ValuesCorresponding Values
xxxxCorresponding ValuesCorresponding ValuesCorresponding Values
xxxxCorresponding ValuesCorresponding ValuesCorresponding Values
xxxxCorresponding ValuesCorresponding ValuesCorresponding Values
xxxxCorresponding ValuesCorresponding ValuesCorresponding Values
atcodedog05
21 - Polaris

Hi @mjh418 

 

1st Question: yes thats correct as long as I designate the FIXED columns as KEYS, all the rest of them (dynamically) will be selected automatically as data columns no matter how many.

 

2nd Question: You can do something like this. It a best practice to use recordID tool when transpose to capture rowID.

atcodedog05_0-1626853000672.png

After tranpose.

1. Using formula to generalize the name.

2. Using crosstab with setting keys as other columns and name is the name and value is value (name and value which came from transpose tool)

 

Hope this helps : )

 

mjh418
8 - Asteroid

@atcodedog05 

 

Thank you so much, sorry i have more questions.

1. If Those "fixed columns" I mentioned above are actually unique dates, (Start and End date), I can use those just like you used RecordID to get the same results right?

 

2. Your regex formula is great for getting rid of the number at the end. 

This works greatly for nothing to 9. Do you have a more comprehensive formula that gets rid of any numbers?

e.g. some of the columns after the digit 9, will become 9_1, 9_2,,,9_2_1 etc etc.

so after every 9, it will add _1 instead of going to 2 digits. 

If there is a formula that can get rid of any numbers at the end, it will be SUPER. 

 

You have helped me so much already..! Thank you very very much  for your precious time.

Labels