How to copy the cumulative number from previous day or 2day before or 3day before if no data.

Yuhei
Yuhei Member
in Magic ETL

I'd like to calculate the cumulative number from daily count and copy the cumulative number from previous day or 2day before or 3day before if no data.

If there are no function on Magic ETL, it is OK to create SQL.

image.png

Best Answer

  • nmizzell
    nmizzell Contributor
    Answer ✓

    Yuhel,

    Great question. This article explains how to downfill values, to get the most recent value even if there is no data.

    How To Downfill Null Values In SQL | Towards Data Science

    You will need to make a slight modification to this logic, where instead of getting the most recent value in the series, you get the cumulative sum of the series.

    You can perform this calculation in SQL or through ETL.

    Here is an example ETL i created to downfill the values. Your ETL should look similar, except have the cumulative sum instead of the first non-null value function applied.

    image.png image.png image.png image.png image.png

Answers

  • nmizzell
    nmizzell Contributor
    Answer ✓

    Yuhel,

    Great question. This article explains how to downfill values, to get the most recent value even if there is no data.

    How To Downfill Null Values In SQL | Towards Data Science

    You will need to make a slight modification to this logic, where instead of getting the most recent value in the series, you get the cumulative sum of the series.

    You can perform this calculation in SQL or through ETL.

    Here is an example ETL i created to downfill the values. Your ETL should look similar, except have the cumulative sum instead of the first non-null value function applied.

    image.png image.png image.png image.png image.png

Categories