Survey banner
The Dataiku Community is moving to a new home! We are temporary in read only mode: LEARN MORE

How to fill cells of column with previous non null value

Level 3
How to fill cells of column with previous non null value

I have a table that is presorted that shows teams and players.


What I'm trying to do is create some kind of calculation to fill my blank cells for team, I want the cells to be filled with the last non null value that precedes that row.

For example, row 3 is blank, the last non null preceding value is row 1 = green, so I want that cell to have the value green also


Another example is row 17 is also blank, the last preceding non null value is row 14 = yellow so I want rows 15-16 to also have the value of yellow


Does that make sense? and is that possible using Dataiku?


I've attached a picture of my ideal output below and a sample dataset.



Operating system used: windows

0 Kudos
3 Replies

Hi @abalo006,

You can do this by using a Window recipe.

Note that the type of the Player column must be "bigint" (not string) so that the rows are sorted correctly.

Window definitions:



Aggregations (don't forget to select "First/Last not null"):



Output dataset:




0 Kudos

Nice one @ZachM, didn't work for me as postgreSQL doesn't support First/Last not null:Screenshot 2024-05-01 at 23.02.44.png

But this can be done in a SQL Recipe relatively easily:

SELECT t1."Player", t1."Team"
, (
    SELECT t2."Team"
    FROM "CT_TEST_book3_copy" t2
    WHERE t2."Player" = (
        SELECT MAX(t3."Player")
        FROM "CT_TEST_book3_copy" t3
        WHERE t3."Player" <= t1."Player"
        AND t3."Team" IS NOT NULL
) AS "Calc_Team"
FROM "CT_TEST_book3_copy" t1;


Screenshot 2024-05-01 at 23.04.31.png







Not easy to do in a Visual recipe but relatively easy in either a SQL recipe or Python recipe. Is your Dataset in a SQL database?

0 Kudos



Setup info

A banner prompting to get Dataiku