How to Get Previous Value for Null Values

How to get Previous Value for Null Values

Please try:

select 
    a.ID,
    ISNULL(a.FeeModeId, x.FeeModeId) FeeModeId,
    ISNULL(a.Name, x.Name) Name,
    a.Amount
from tbl a
outer apply
(select top 1 FeeModeId, Name 
    from tbl b 
    where b.ID<a.ID and 
        b.Amount is not null and 
        b.FeeModeId is not null and 
        a.FeeModeId is null order by ID desc)x

OR

select 
    ID,
    ISNULL(FeeModeId, bFeeModeId) FeeModeId,
    ISNULL(Name, bName) Name,
    Amount
From(
    select 
        a.ID , a.FeeModeId, a.Name, a.Amount, 
        b.ID bID, b.FeeModeId bFeeModeId, b.Name bName,
        MAX(b.FeeModeId) over (partition by a.ID) mx
    from tbl a left join tbl b on b.ID<a.ID
    and b.FeeModeId is not null
)x 
where bFeeModeId=mx or mx is null

Function to REPLACE* last previous known value for NULL

Assuming the number you have is always increasing, you can use MAX aggregate over a window:

SELECT dt
     , country
     , cnt
     , MAX(cnt) OVER (PARTITION BY country ORDER BY dt)
  FROM #data

If the number may decrease, the query becomes a little bit more complex as we need to mark the rows that have nulls as belonging to the same group as the last one without a null first:

SELECT dt
     , country
     , cnt
     , SUM(cnt) OVER (PARTITION BY country, partition)
FROM (
    SELECT country
         , dt
         , cnt
         , SUM(CASE WHEN cnt IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY country ORDER BY dt) AS partition
    FROM #data
) AS d
ORDER BY dt

Here's a working demo on dbfiddle, it returns the same data with ever increasing amount, but if you change the number for 08-17 to be lower than that of 08-16, you'll see MAX(...) method producing wrong results.

How to replace null values with previous non null values

With Talend, you can use a tMap with intern variables to use previous values.
You'll need three variables (from the central tab in your tMap) :

• current : row1.yourValue // cache your input data for current line

• previous : Relational.ISNULL(Var.current)?Var.previous:Var.current //if current line is null, keep the previous one. Otherwise replace 'previous' with current line.

• getPreviousIfNull : Relational.ISNULL(row1.yourValue)?Var.previous:Var.current

  //if your input data is null : get the previous one. Otherwise get the current one.

row1.yourValue is your input data.

Then you extract Var.getPreviousIfNull to your output.

Fill Null Values with Last Previous Value and add 1 as a continuous integer for every value going forward - Big Query

I'm using similar data as you posted in your question.

with data as (
 SELECT 'Americas_1 '  as id,1 as activity, 'America' as region union all
 SELECT 'Americas_2 '  as id,2 as activity, 'America' as region union all
 SELECT 'Americas_3 '  as id,3 as activity, 'America' as region union all
 SELECT 'Americas_4 '  as id,4 as activity, 'America' as region union all
 SELECT null  as id,null as activity, 'c' as region union all
 SELECT null  as id,null as activity, 'a' as region
)

In the subquery data, I just have the sample data. In the second subquery data2, I added a column number, this column adds the row_number when the activity column is null, if it is not null add a 0. The column new_activity just puts the same numbers when activity is not null.

Here you can see the complete query.

with data as (
 SELECT 'Americas_1 '  as id,1 as activity, 'America' as region union all
 SELECT 'Americas_2 '  as id,2 as activity, 'America' as region union all
 SELECT 'Americas_3 '  as id,3 as activity, 'America' as region union all
 SELECT 'Americas_4 '  as id,4 as activity, 'America' as region union all
 SELECT null  as id,null as activity, 'c' as region union all
 SELECT null  as id,null as activity, 'a' as region
), data2 as (
 
select id,activity, region, 
IF (activity is null,ROW_NUMBER() OVER(ORDER BY activity),0) as number,
IF(activity IS NULL,
last_value(activity ignore nulls) over (order by activity RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ,
activity ) as new_activity
from data
group by id,activity, region
order by activity asc nulls last
)

This query displays these columns ID, activity, region; and in the last column new_Activity, I sum the column number and new_activity from the subquery data2

select id, activity, region, (number+new_activity) as new_Activity from data2
order by activity asc nulls last

This is the output of the query.

Python: Function to fill in the previous row of a non-null value

You can use pandas where() + bfill() to fill previous row with a "check" value, so my_func() will test it to calculate "final times".

df['Combined Time'] = df['Combined Time'].where(
                            df['Combined Time'].bfill(limit=1).isnull(), 
                            df['Combined Time'].fillna(pd.Timedelta('0:00:00')))

Modified function:

def my_func(x):
    if pd.notnull(x['Combined Time']):
        if x['Combined Time'] == pd.Timedelta('0:00:00'):
            return pd.NaT
        else:
            return x['Combined Time']
    else:
        return x['Shift Time']

Apply:

df['Final Times'] = df.apply(my_func, axis=1)
df

Result:

    Shift Time       Combined Time      Final Times
0   0 days 13:00:00  NaT                0 days 13:00:00
1   0 days 07:00:00  0 days 00:00:00    NaT
2   0 days 01:19:00  0 days 08:19:48    0 days 08:19:48
3   0 days 07:00:00  NaT                0 days 07:00:00
4   0 days 14:00:00  0 days 00:00:00    NaT
5   0 days 02:00:00  0 days 16:00:00    0 days 16:00:00

Load data:

(Please paste your data and format as code instead of screenshots)

df = pd.DataFrame({'Shift Time': [pd.Timedelta('13:00:00'), 
                             pd.Timedelta('7:00:00'),
                             pd.Timedelta('1:19:00'),
                             pd.Timedelta('7:00:00'),
                             pd.Timedelta('14:00:00'),
                             pd.Timedelta('2:00:00')],
                  'Combined Time': [np.nan, np.nan, 
                               pd.Timedelta('8:19:48'), 
                               np.nan, 
                               np.nan, 
                               pd.Timedelta('16:00:00')],
                  'Final Times': np.nan * 6})

---

Related Topics