How to replace a column value by its previous value with condition-group wise

Viewed 152
Group Rank Category
A 1 New business
A 2 Adjustment
A 3 Adjustment
A 4 Renewal
A 5 Adjustment
A 6 Cancellation
B 1 New Business
B 2 Renewal
B 3 Adjustment
B 4 Cancellation
C 1 Adjustment

The goal is to replace all the 'Adjustment' with the above value group wise. The category values for each group could start with any value except Cancellation. If it starts with Adjustment itself, then it should be left untouched.

Output:

Group Rank Category
A 1 New business
A 2 New business
A 3 New business
A 4 Renewal
A 5 Renewal
A 6 Cancellation
B 1 New Business
B 2 Renewal
B 3 Renewal
B 4 Cancellation
C 1 Adjustment
3 Answers

It is possible. The idea is to use LAG with IGNORE NULLS option(and nullify 'Adjustment' values):

When the IGNORE NULLS clause is set, any row whose expression evaluates to NULL is not included when offset rows are counted. If { IGNORE | RESPECT } NULLS is not specified, the default is RESPECT NULLS.

SELECT group, rank, category,
   CASE WHEN rank = 1 THEN category
        WHEN category = 'Adjustment' 
        THEN LAG(IFF(category='Adjustment',NULL,category)) IGNORE NULLS 
             OVER(PARTITION BY group ORDER BY rank)
        ELSE category
   END AS new_category
FROM tab

db<>fiddle demo

Output:

enter image description here

You could use a lag statement enclosed in an iff.

select group, 
       rank,
       iff(category = 'Adjustment',
           lag(category, 1, category) over (partition by group order by group, rank),
           category) as category
from table

This does not answer the question exactly however I thought it provides a different approach to the solution.

Instead of the lag() approach (which is the perfect solution for this problem) you could choose to ignore the 'Adjustments' altogether with the exception of the initial record.

The result is similar it just removes the repeated rows (which don't really add any value do they?).

Keeping the Rank shows the number of rows removed ... and at scale would reduce the size of the dataset without removing any information -> which I think is the goal of any data practitioner.

Any thoughts welcomed :-)

enter image description here

with cte as (
SELECT 'A' AS grp,  1 AS rnk,   'New business' AS category    FROM DUAL 
UNION ALL SELECT 'A' AS grp,    2 AS rnk,   'Adjustment'      FROM DUAL 
UNION ALL SELECT 'A' AS grp,    3 AS rnk,   'Adjustment'      FROM DUAL 
UNION ALL SELECT 'A' AS grp,    4 AS rnk,   'Renewal'         FROM DUAL 
UNION ALL SELECT 'A' AS grp,    5 AS rnk,   'Adjustment'      FROM DUAL 
UNION ALL SELECT 'A' AS grp,    6 AS rnk,   'Cancellation'    FROM DUAL 
UNION ALL SELECT 'B' AS grp,    1 AS rnk,   'New Business'    FROM DUAL 
UNION ALL SELECT 'B' AS grp,    2 AS rnk,   'Renewal'         FROM DUAL 
UNION ALL SELECT 'B' AS grp,    3 AS rnk,   'Adjustment'      FROM DUAL 
UNION ALL SELECT 'B' AS grp,    4 AS rnk,   'Cancellation'    FROM DUAL 
UNION ALL SELECT 'C' AS grp,    1 AS rnk,   'Adjustment'      FROM DUAL )

select  
    cte.* 
from 
    cte 
left outer join 
    (select grp,rnk,category from cte where iff(rnk=1,'z',category)!='Adjustment') yay on yay.grp cte.grp and yay.rnk=cte.rnk 
where yay.grp is not null; 
Related