Multi field formaula - replace nulls with other fields value

[Shaman]

I have a data set and want to replace nulls with respective values from other fields:

Region	Period 1	Period 2	Period 3	Period 4	Period 5	Period 6	Period 7	Period 8	Period 9	Period 10	Period 11	Period 12	Right_Period 1	Right_Period 2	Right_Period 3	Right_Period 4	Right_Period 5	Right_Period 6	Right_Period 7	Right_Period 8	Right_Period 9	Right_Period 10	Right_Period 11	Right_Period 12
AS	300	320	325	330	335	340	345	350					300	300	300	300	300	300	300	300	305	310	315	320
EU	100	150	155	160	165	170	175	180					100	100	100	100	100	100	100	100	105	110	115	120
LA	400	420	425	430	435	440	445	450					400	400	400	400	400	400	400	400	405	410	415	420
ME	200	220	225	230	235	240	245	250					200	200	200	200	200	200	200	200	205	210	215	220
NA	500	550	555	560	565	570	575	580					500	500	500	500	500	500	500	500	505	510	515	520

Periods 1 to 8 shall remain, Period 9 to 12 shall get the value from columns "Right_Period x"

I tried with Multi field formula:

if isNull([_CurrentField_])
Then [Right_[_CurrentField_]]
else [_CurrentField_]
endif

I get an error, "", + and & after "Right_" does not help, any idea how to solve?

[Hollingsworth]

The Multi-Field Formula tool does not work that way so it's not that you aren't using it correctly.

Are those four fields (9-12) the only fields that will be potentially null? Or do all 12 fields have the potential to be null? 

[Emil_Kos]

Hi @Shaman,

As I didn't know how many columns your true data have I have created a dynamic solution that should work for any number of columns:

The downside of this solution is that the column names don't have underscore anymore. As I was thinking it isn't a big deal I have made a shortcut for myself. They are in the same order.

Like @Hollingsworth you aren't able to achieve the desired result with the multifield formula. You need something more complex. 

[KarolinaRoza · Accepted solution]

Hi @Shaman ,

I am not sure if Multi-Field formula will work, but I prepared solution which solve your problem without using that Tool.

You can Transpose rows to column, then check for example: if [Period 12] is null in the same time then [Right_Period 12] is not null, and if yes then supplement the value from Right_Period column.

To do it you can use Multi-Row formula instead.

Then transpose data back using Cross-Tab.

Please check if it works for you,

Karolina

[Emil_Kos]

Hi @KarolinaRoza,

Regex and multi-field formula. That is much more efficient than my solution. Nicely Done! 🙂

[apathetichell]

I just wanted to make a workflow and name it "let the right one in..." and I like Dynamic Select... 

[Shaman]

Hi @apathetichell 

very nice solution, just need dynamic approach like the solution from Karolina