Is there a way to duplicate a value one row down?

Options
muahmmad_zaidi
muahmmad_zaidi Member
in Magic ETL

I have a column with a numeric value and null value every other cell. I want to replace the null cells with the numeric value that is directly above it.

Thanks in advance!

Tagged:

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Options

    You can use the window function tile in Magic ETL. The function want to apply is LAG.

    This article gives more info, scroll down to the Rank & Window Tile section:

    https://domo-support.domo.com/s/article/360044876094?language=en_US


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • muahmmad_zaidi
    muahmmad_zaidi Member
    Options

    @ST_-Superman-_

    Is there a way I could do this using an add formula tile; the Rank & Window tile does not seem to work.

    For context, every odd row has a value in this column and every even row is null. I'm trying to fill the cells in the even rows with the value right above it.

    I'm trying to use the lag function in the add formula tile, but is seems that it isn't a recognized function for the add formula tile.

  • rco
    rco Contributor
    edited June 2023
    Options

    Could you clarify the problem you're having with Rank & Window? @ST_-Superman-_ is correct in saying that the Lag function is what you want.

    A lag of 1 will give you a new column whose values are all offset by 1 from those of its source column. That's not a complete solution, though, since you'll still have nulls in every other value. The next step is to use Add Formula with the "coalesce" function to make a column that has the non-null value from either the original or the lagged column, like so: COALESCE(my_original_column, my_lag_colum)

    Randall Oveson <randall.oveson@domo.com>

Categories