I have a dataset that looks like this:
A B 0 1 0 1 0 1 1 1 1 1 1 1 0 1 0 1 0 1 0 1 0 1 0 1 0 0 0 0 0 0 1 0 1 0
I want to create a variable that equals 1 when A and B are 1 or B equals 1 and A has equaled 1 since B has equaled 1. Essentially we need to remember if A has equaled 1 in the past.
A B C 0 1 0 0 1 0 0 1 0 1 1 1 1 1 1 1 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 1 1 0 0 0 0 0 0 0 0 0 1 0 0 1 0 0
Assuming you have at least one other column (call it seq_nbr) that gives you the necessary row ordering, an OLAP function with RESET WHEN can determine if A is / has been 1:
SELECT a, b,
CASE WHEN b=1 AND (MAX(a) OVER (ORDER BY seq_nbr RESET WHEN b=0 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))=1 THEN 1 ELSE 0 END AS c
FROM theTable
ORDER BY seq_nbr;
Hi JOHN_,
Slightly different from Fred's answer but with the same logic.
Data
create multiset volatile table mvt_data, no log ( mvt_PI byteint , colA byteint , colB byteint , ordr byteint ) primary index (mvt_PI) on commit preserve rows; insert into mvt_data values (0, 0, 1, 1); insert into mvt_data values (0, 0, 1, 2); insert into mvt_data values (0, 0, 1, 3); insert into mvt_data values (0, 1, 1, 4); insert into mvt_data values (0, 1, 1, 5); insert into mvt_data values (0, 1, 1, 6); insert into mvt_data values (0, 0, 1, 7); insert into mvt_data values (0, 0, 1, 8); insert into mvt_data values (0, 0, 1, 9); insert into mvt_data values (0, 0, 1, 10); insert into mvt_data values (0, 0, 1, 11); insert into mvt_data values (0, 0, 1, 12); insert into mvt_data values (0, 0, 0, 13); insert into mvt_data values (0, 0, 0, 14); insert into mvt_data values (0, 0, 0, 15); insert into mvt_data values (0, 1, 0, 16); insert into mvt_data values (0, 1, 0, 17);
Query
select ordr , colA , colB , max(colA * colB) over(order by ordr asc reset when colB = 0 rows unbounded preceding) as colC from mvt_data where mvt_PI = 0 order by ordr asc; ordr colA colB colC ---- ---- ---- ---- 1 0 1 0 2 0 1 0 3 0 1 0 4 1 1 1 5 1 1 1 6 1 1 1 7 0 1 1 8 0 1 1 9 0 1 1 10 0 1 1 11 0 1 1 12 0 1 1 13 0 0 0 14 0 0 0 15 0 0 0 16 1 0 0 17 1 0 0
Hi Fred,
That's great thanks!
I'm having a problem when using this derived variable C in a where statement.
WHERE c=1
I get the error:
SELECT Failed. [5479] Ordered Analytical Functions not allowed in WHERE Clause.
Any work arounds to this?
Thanks, John.
You can filter on analytical functions using the QUALIFY clause.
Or use a derived table expression:
SELECT * FROM ( query without the WHERE clause ) AS Q WHERE c=1
For the example data there's no need to use RESET WHEN, it depends on the expected result when the next row has a B 1 again :-)