I'm working on an ETL. I want to create a unique ID for each row of data with the caveat that the ID should be the same as the previous value if a value 0 appears in a flagged column. I was thinking of doing this through a window function of row number and then a function tile, but I can't figure out how to achieve this (and then want to sum the order values within the flagged areas (this is within the confines of the same account only). Here's an example:
Account # Close Date Flag Unique ID Order Value Total Order Value
ABCD 01/22/2022 1 1 $50 $50
ABCD 02/23/2022 1 2 $100 $310
ABCD 04/21/2022 0 2 $200 Null/N/A
ABCD 05/11/2022 0 2 $10 Null/N/A
EFGH 01/22/2022 1 3 $50 $50
IJKL 02/25/2022 1 4 $10 $30
IJKL 04/21/2022 0 4 $20 Null/N/A
IJKL 05/11/2022 1 5 $70 $70
Any ideas? Thanks!