Skip to content
Advertisement

Big query/sql to maintain the same data from next row onwards in the same column itself, got based on some condition in the current row

I am working on one of the usecase where I need to implement one of the logic to get the relevant data .

eg: If you will see the image :

The column = new_TARIFF_ALLOWANCE_DATA is calculated as

case when gift_given!=0 or gift_received!=0 then TARIFF_ALLOWANCE_DATA – gift_given + gift_received else remain same whatver it is as per last row calculation from next row onwards‘ as new_total_allwance

So basicall I want in the same column based on the condition whatver I got will maintain the same value from the next rown onwards in the same column

enter image description here

I tried with the approach if (current_row = previous_row then previous_row , current_row) but not working.

Advertisement

Answer

An approach using navigation functions to get the last populated values from gift_given and gift_taken:

Output: Expected output

More about LAST_VALUE navigation function: https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#last_value

Advertisement