How to fill cells of column with previous non null value

abalo006 · May 1

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

Zach · May 1

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:

Turribeach · May 1

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?

Turribeach · May 1

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

How to fill cells of column with previous non null value

Answers

Categories

Setup Info

Tags