Point/Reference a field Dynamically

[ntajab]

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 Realized	Field_38_9
null	null
null	null
null	null
null	null
null	42695978
null	35848136
null	3984517
null	29661140
null	27527688
null	5195436
null	2262733
null	4044951
null	3049943
null	25646312

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.

[nagakavyasri]

Can you provide sample input and what are you expecting as output?

[ntajab]

Hello @nagakavyasri here is the possible situations for the input and desired output.

[Qiu]

@ntajab 
It seems interesting to me.

If I understand your question correctly, the column names for "Right Next Column" and "Left Next Column" are dynamic, so can not use the column names.

Since there is similar tool as Multi-Row Formula tool for columns , so we should convert the data to a format that Multi-Row Formula tool can be applied.

[ntajab]

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?

[Qiu]

@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]

@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.