I have a data set and want to replace nulls with respective values from other fields: RegionPeriod 1Period 2Period 3Period 4Period 5Period 6Period 7Period 8Period 9Period 10Period 11Period 12Right_Period 1Right_Period 2Right_Period 3Right_Period 4Right_Period 5Right_Period 6Right_Period 7Right_Period 8Right_Period 9Right_Period 10Right_Period 11Right_Period 12AS300320325330335340345350 300300300300300300300300305310315320EU100150155160165170175180 100100100100100100100100105110115120LA400420425430435440445450 400400400400400400400400405410415420ME200220225230235240245250 200200200200200200200200205210215220NA500550555560565570575580 500500500500500500500500505510515520 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? dataset.xlsx

Multi field formaula - replace nulls with other fields value

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?

dataset.xlsx

Preparation

Accepted answers

KarolinaRoza

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

multi-row formula and transpose.yxmd

All comments

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.

Dynamic Multi Field.yxmd

KarolinaRoza

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.