Replace with last non-null or non-zero in data flow

Depending on the size of your dataset, you could use some nested queries to update the data. 

Here's an example that should work for the sample data you provided, supposing the columns are called `Date_Column` and `Value_Column` and are on a table called table_name:

SELECT
  `Date_Column`
  ,CASE
    WHEN `Value_Column` = 0 
    THEN (
          SELECT 
            `Value_Column` 
          FROM 
            table_name t2 
          WHERE 
            `Date_Column` = (
                             SELECT 
                               MAX(`Date_Column`) 
                             FROM 
                               table_name t3 
                             WHERE 
                               `Date_Column` < t1.`Date_Column` 
                               AND `Value_Column` > 0
                             )
          ) 
    ELSE `Value_Column`
  END AS `Value_Column`
FROM
  table_name t1

 If your dataset is very large, this may take a while to run, but it should provide the functionality you're looking for.  

For each record, it will check to see if the value is 0. If it is, it will search for the value associated with the max date that comes before the date for the record it's currently looking at. Also note that it will fail if you have multiple records with the same date. There are some ways to possibly fix that, though.

Let me know if it helps!

Prajju,

In MySQL, you could try using something like this, which uses replacement variables instead of self joins:

SELECT
    `Date_Column`
    , (@LastValue := CASE WHEN IFNULL(`Value_Column`, 0) = 0 THEN @LastValue ELSE `Value_Column` END) AS `Value_Column`

FROM
    table_name
, (SELECT @LastValue := 0) lv

I hope that helps!

Prajwal,

You'd want to first make sure the data is sorted by the two dimensions you're using as your partitions. Then you'll need to include two new replacement variables to keep track of the values in the dimension columns and mark when they've changed.

The first transform would be to sort the original table:

SELECT * FROM `propagate_test` ORDER BY `Category_Column`, `Date_Column`

Call the output table sorted_table.

Then the main transform could look like this:

SELECT
    (@LastValue := CASE
                                  WHEN (`Category_Column` != @LastCategory OR `Date_Column` != `Date_Column`) THEN `Value_Column`
                                 WHEN IFNULL(`Value_Column`, 0) = 0 THEN @LastValue
                                 ELSE `Value_Column`
                             END) AS `Value_Column`
    , @LastDate := `Date_Column` AS`Date_Column`
    , @LastCategory := `Category_Column` AS`Category_Column

FROM
    table_name
    , (SELECT @LastValue := 0) lv
    , (SELECT @LastDate := NULL) ld
    , (SELECT @LastCategory := NULL) lc

Test and tweak that to fit your needs. I hope it helps!