Help needed: Multi Field Formula move data

[EddyPete]

I have 10 fields with embedded values that I want to pull out and move to a single field. The Multi-Field Formula tool looks like just what I need. I strung two of them together, and the first tool has all 10 fields selected to create new fields with the following formula:

 IF [_CurrentField_] IN
("83036","67042","92134","76977","77080","3075F","3075F","3077F",
"3078F","3079F","3080F","2022F","3044F","3045F","3046F","4010F",
"3060F","3061F","3062F","3066F","1170F","1158F","1159F","1160F",
"1126F","G0202","G0328","G0104","G0121","G0130")
THEN [_CurrentField_] ELSE Null() ENDIF

That works fine. All of the values in the original fields containing values in the IN statement above are moved to 10 new fields. The second tool is supposed to select all 10 of the new fields and move the values to a single field:

[line_procedure_cd] == [_CurrentField_]

Except that it doesn't. The new fields have a "0" written in them and the [line_procedure_cd] field is unchanged. How do I write the expression to write the data from the new fields to [line_procedure_cd]? Only one of the 10 fields has a value in it for a given record, so there's no chance of multiple values being written to [line_procedure_cd].

[Aguisande]

@EddyPete

I believe in this line: 

[line_procedure_cd] == [_CurrentField_]

The expression should be only [_CurrentField_], since [line_procedure_cd] is the name of the new "unified" field right?

If I'm not getting it wrong, maybe, because they are String fields, and there's just one value per row for all the fields, the new field can be a simple formula like:

[Field1] + [Field2]+etc...

If I got it wrong, maybe if you post a sample of your data, I can be of more help.

[EddyPete]

[line_procedure_cd] is a existing field that I want to populate with the contents of the 10 new fields I created. I got used to the different way that Alteryx handles conditional statements in the Formula tool (i.e., specifying the target field at the top of the configuration panel, then the conditional statement below), but I can't figure out how to reference that target field in the Multi Field Formula tool. I've included the part of the workflow in question (the field names have been changed to protect the innocent).

[patrick_digan · Accepted solution]

@EddyPete Perhaps the transpose tool would work? I've attached an example. I also used a join-union to add back any rows that didn't have any of the values you're searching for. 

[EddyPete]

@patrick_digan  That is an interesting strategy that I'll try. Thank you. I'm going to leave this question "unsolved"
 for a little longer to see if there's an answer to my original question. 

[patrick_digan]

@EddyPete I don't think your second multi-field formula is going to do the trick.  That tool only allows you to overwrite the existing data in each of the selected fields, or copy each of the selected output fields. It wouldn't allow you to keep resetting another field like you're attempting (although that would be a pretty useful feature!).

For what it's worth: if your columns aren't changing, you could replace your second multi-field formula tool with a long formula like: 

Max([New_Procedure1],[New_Procedure2],[New_Procedure3],[New_Procedure4],[New_Procedure5],[New_Procedure6],[New_Procedure7],[New_Procedure8],[New_Procedure9],[New_Procedure10])

The big disadvantage is that it wouldn't accept a dynamic list of fields since you have to write each named column into the formula. The transpose tool would allow for an unlimited number of columns.

[EddyPete]

@patrick_digan  I see what you mean now about the Multi Filed Formula tool vs the Transpose tool (which I did use successfully). I'll have to keep that filed for future reference. Thnaks for the help.