Filling missing cells in vertically down based on previous value

[wisemanleo]

I have a dataset that looks like this:

1. I want the rows above “Total First Citizens” to be labelled “First Citizens”, and,

2. I want the rows below to be labelled “First Citizens Aflac” (until the row value, which takes the baton)

I can easily remove the “Total” lines with a row filter, but a bit stumped on this piece.

Many thanks in advance!

[lelloba]

Hello Wisemanleo,

can this solution work for you?

Split and label rows – lelloba

RB

[aworker]

Hi @wisemanleo

Please find below an alternative solution:

20220525 Pikairos Filling missing cells in vertically down based on previous value.knwf (41.2 KB)

The trick here is to fill up empty values in two steps, first for “next values” and then for “previous values”.

Hope it helps.

Best
Ael

[wisemanleo]

Hey lelloba - unfortunately this doesn’t scale as this solution requires hardcoding of the category fields.

[wisemanleo]

This worked. I’m still investigating, but is it possible that the rule Missing Value node is scanning all columns, or do I have to explicitly declare which columns it should not scan?

[wisemanleo]

Book1.xlsx (15.6 KB)
The solution is very close. I realized that the second column is somewhat a function of the first column, so, ignoring the ‘Total’ lines as I can easily remove this with a filter, but how to this column - subcolumn filldown?

We kind of have two scenarios, with the first being:
Simple

NCFMC PPP fills down, Total filtered out (I got this part), then start new with First Citizens and fill down. The second column has nothing to fill and should stay empty.

But then, gets somewhat tricky:

And the rows of actual data are somewhat in between the first and last row of the second column:

Is this possible to process?

Overall workflow so far

[wisemanleo]

I was able to solve this with a minor modification of the above workflow - just required a delicate dance between the Missing Values and Row Filter nodes, as Row Filtering a column too early, removes necessary columns needed downstream for the Missing Values node.

Thanks all - Missing Values node for the win!