TERADATA: Is it possible to ignore rows in an OLAP partition when the condition is met and still pass the value down when it isn't met?

Question

I'm partitioning data based on a customers previous order, so if the customer previously added a service to their account (they either have the service or they don't), I want that value to carry down to the next row for that customer for all orders regardless of the order status, but I don't want canceled order services to be calculated with the next order, I want to skip those rows and bring down the value from the previously completed order. Does anyone know if this is possible? If I add the field into the Partition By clause, it'll partition by order status instead of reporting the order status from the previous completed order.

(
Sum
    (
    SUBSCR1_ORD
    )
    Over 
        (
        PARTITION BY ACCT_NO
        ORDER BY ORDER_DATE
        ROWS BETWEEN 1 Preceding AND 1 Preceding
        )
)
AS EXISTING_SVC1

This is what I'd want the results to look like for the EXISTING_SVC columns based on activity in the SUBSCR1_ORD column with special handing on ORDER_STATUS

ACCT_NO	ORDER_DATE	ORDER_STATUS	SUBSCR1_ORD	SUBSCR2_ORD	EXISTING_SVC1	EXISTING_SVC2
1234	6/5/2022	Complete	1	null	0	0
1234	6/6/2022	Canceled	-1	1	1	0
1234	6/7/2022	Complete	null	1	1	0

Answer 1

Use LAG with IGNORE NULLS and a CASE expression to "pull down" the prior value.

SELECT Acct_No, Order_Date, Order_Status, Subscr1_Ord, Subscr2_Ord,
    LAG(CASE WHEN Order_Status='Canceled' THEN NULL ELSE Subscr1_Ord END,1,0)
        IGNORE NULLS
    OVER(PARTITION BY Acct_No ORDER BY Order_Date)
    AS Existing_Svc1,
    LAG(CASE WHEN Order_Status='Canceled' THEN NULL ELSE Subscr2_Ord END,1,0)
        IGNORE NULLS
    OVER(PARTITION BY Acct_No ORDER BY Order_Date)
     AS Existing_Svc2   
FROM MyTable
ORDER BY Order_Date;