Fill non - null with existing value

[shreya2811]

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!

[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?

[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

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

[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?