Point/Reference a field Dynamically

Question

Hello,

Like in the Multi-Row Formula Tool we have the ability to set the row below or the row above in the expression area, I was looking to find a similar logic/functionality/solution in the Multi-Field Formula Tool but Field Right or Field Left instead.

To give more context of what I am trying to achieve, I'll describe as best as I can the situation in the following.

I'm building a workflow that is pulling data from an xlsx file, and for some unknown reason one of the fields the data is being parsed/read in a field that is not correspondent to its label, and that is the puzzle I want to solve. As I don't want to manually edit the raw source data to prevent this to happen when running the workflow, I am trying to find a solution that will dynamically check if the data was parsed in the next right or next left field and then merge the data into its correspondent Field Label.

Dollars RealizedField_38_9nullnullnullnullnullnullnullnullnull42695978null35848136null3984517null29661140null27527688null5195436null2262733null4044951null3049943null25646312

As you can see in the above example the values of the "Dollars Realized" field was parsed/read to the next Right column (it could have be parsed/read into the next Left column), thus what I want to do is a some sort of IF statement logic like:

IF IsNull([Dollars Realized]) AND !IsNull("Right Next Colum") THEN "Right Next Column" ELSEIF

IsNull([Dollars Realized]) AND !IsNull("Left Next Colum") THEN "Left Next Column" ELSE

[Dollars Realized]

ENDIF

The solution does not have to use a formula tool, I just used the above to explain what is the conditions of my problem and what the desired output.

I am asking here because I feel like the solution I have in my mind is over complicated and wanted to see if more experienced users could give a some help on this.

Thank you all in advance for the patience and help.

ntajab · Answer

@Qiu

The criteria I was using and thought it would be useful, is the fact that "Dollars Realized" column will always have a Named and Populated column in its next left or in its next right and therefore it will have a "Unnamed" but Populated column in its next left or in its next right with the data that should have been parsed under the column "Dollar Realized".

Now that I've written the above, maybe the solution is to just find this situation using the Transpose tool in the headers, and then just renamed it accordingly and finally removing the null "Dollar Realized" column.

Qiu · Answer

@ntajab 
I found you provided some new input files, which are different from my assumtion.
your left or right next column are not always present, right?

In such case, we probably going to need some "Criteria" of the naming convention of your left or right columns, otherwise, we can not knwo which is the "real" next right or left columns.

ntajab · Answer

Hello @Qiu,

Yes your understanding was correct "Right Next Column" and "Left Next Column" are dynamic, that is why I want to determine the right next or the left next in reference to the "Dollars Realized" column dynamically.

I've run your workflow, and it seems to me that in your TEXT Input tool, your example does not reflect the situation I've exposed here, or I miss understood your reply. (Looks like you've already done some steps before the result you have in the Text Input Tool data) Am I assuming correct?

If so could you show what steps you did before?