Dynamic or Unknown fields in Formula tool

[WestonProhaska]

Hi All, 

Thanks for the help in advance. 

I have data where its basically a big mapping exercise. I have two questions. 

Mapped key 1 (in other file)	Alternate Key 2 (Possibly in other file)	Alternate Key 3 (Possibly in Other file)
A	B	C

I want to create fields with the formula tool where I use the Mapped Key fields. However, the key fields won't always be present in the second file. These key fields all come from file B and all tie to the same field in File A, but won't always exist. For example Key1 might exist in one run, but in another key 2 will exist in its place. Is there a way to to use the formula tool to include dynamic/unknown fields?

IE:

Create Field A:  [Mapped Key1] OR if exists [Alternate Key 2 ] OR If exists [Alternate Key 3]. 

Or is there a better way of doing this?

[binuacs]

@WestonProhaska One way of doing this is by transposing the data and creating your field with the formula tool then cross-tab the data

[DanFlint · Accepted solution]

Hi @WestonProhaska 

The way I would approach this is by ensuring the different possible key fields are present on the file and using a sequence of IF statements in priority order.

By putting your key fields into a text input and then using a filter where the filter condition is "0", we can ensure only column names are taken from the text input.

I then union this onto the data to ensure the fields exist if they weren't on the main data.

I've then used a nested IF statement to pull the correct key value before using the data cleansing tool selecting "remove null columns" to remove the potential key fields that didn't exist in your data.

Hope this helps!