Fill non - null with existing value

Hi All,

A simple question:

Have a column with either 1 value say 23-05-2020 OR null. Want to replace all null values with this non-null value(23-05-...)

I do not want to hard code it as this value will change as per the new input file. I tried multi row but it doesn't do anything. (code below) , All null values are indeed null(checked through data cleansing).

If isnull([Week Ending]) then [Row-1:Week Ending] else [Week ending] endif

Any help is appreciated. thanks!

Workflow

Accepted answers

Luke_C

@shreya2811 In the top section is there a reason you're updating the REGION field rather than the WEEK_ENDING field? That seems to be the issue.

All comments

Luke_C

Hi @shreya2811

Your formula seems fine, I presume it's not working because the first row in the dataset could be null? One simple thing you could try is sorting the data so the non-null records appear first and fill down in your multi-row formula correctly. One other approach is to use a summarize tool to get the max value and append it back to the main dataset, assuming there is only 1 value you expect to repeat in that column.

shreya2811

@Luke_C hi Luke, thanks for your response. Tried sort and multi row too. This way my all null valued rows are first and then non-null valued rows. Still no luck

Luke_C

Hi @shreya2811

Just mocked up a simple example, seems to work fine with your exact formula. Can you take a look at the configs and see if there's a significant difference?

New Workflow10.yxmd

messi007

@shreya2811,

Please see below

if !isnull([Row+1:Week Ending])
  then [Row+1:Week Ending]
elseIf isnull([Week Ending]) 
  then [Row-1:Week Ending] 
else [Week ending] 
endif

Attached the workflow,

Regards

MultiRow_20210608.yxmd

shreya2811

Thanks Messi, no luck yet. This is the output I get for those colums:

shreya2811

Thats so kind thanks Luke, I still get below output , can you share your max option?

Luke_C

Hi @shreya2811 I don't see a sort tool in the screenshot you gave @messi007 take a look at the workflow I attached and try to apply it to yours, otherwise please share a sample of your data.

Max option is attached, but multi-row should work.

New Workflow10.yxmd

shreya2811

Here you go, did sorting as well but still null values

Luke_C

Are you grouping by any fields in the multi-row tool? Can you share the configurations?

shreya2811

Yes

Luke_C

That doesn't look like the multi-row formula tool. Can you share those configurations please? Have you tried to review the solutions posted to see where the gap might be?

shreya2811

no grouping in multi row , yea have looked at some other solutions are tried it but still stuck

Luke_C

@shreya2811 In the top section is there a reason you're updating the REGION field rather than the WEEK_ENDING field? That seems to be the issue.

shreya2811

Worked like a charm! That was a very stupid mistake I did, you are awesome Luke, thanks for spending the day fixing this..have a GREAT day, will bother you again as I need to build a complex workflow using these results. Ciao 🙂

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280