Applying the same value to all of the same type

[nightsky3]

I’ve got some empty cells I’d like to fill in. Any row from the continent that already has a date, I want to use it to replace all rows from that continent with the same date. But each date is different depending on the continent.

For example, any row/country from the continent ASIA will get the 12/1/2019 applied to it. Like the picture below, before and after:

and what I want it to look like afterwards:

Any help is appreciated!!

[andrejz]

Hi,

One solution is to sort the data first by the continent column and next by date column and then use the “Missing Value” node to fill the empty cells of the date column with the previous value.

Regards

[bruno29a]

Hi @nightsky3 , this is a bit hard to answer as we don’t see all the data.

For example, are there any continents that may have more than 1 date?

You can use the Missing Value node to fill empty cells with the previous value as @andrejz mentioned, but this only works if your data is sorted properly as it will apply the rule based on the order of the data from the input.

[nightsky3]

Makes sense. Each continent does have only 1 unique date.

[bruno29a]

Hi @nightsky3 , ok, that’s good that the continents only have 1 date.

I just noticed from your sample that it’s not always the first entry of the continent that has the date, so the Missing Value might not work as is.

BTW, Japan is in Europe? I changed Japan for France

The approach I would use is to create a separate table for each continent and with their respective date. You can do this via a GroupBy. Just group by continent, but the trick here is to do the aggregation on the date column, but without the Missing values:

Just make sure that “Missing” box is unchecked. For the Aggregation method, you can use First, or Last, or Minimum, or Maximum, etc, it does not matter as it will have only 1 date per continent.

This gives you the following table:

After that, just join with the original data on continent, and you get this:

This is how the whole workflow looks like:

Here’s the workflow: Applying the same value to all of the same type.knwf (9.9 KB)

[nightsky3]

Yes, I noticed my mistake about Japan being in Europe haha. Thank you for your help, this solved it for me!