Cumulative flag=1

[QuentinDeci]

Hello everybody,

I have a question for you:

I search to calculate the cumulative number of days where the flag = 1 (if the flag = 0 the cumulative stop).

For exemple:

mag	cug	date	flag
1	1010	09/06/2021	1
1	1010	09/05/2021	1
1	1010	09/04/2021	1
1	1010	09/03/2021	0
1	1010	09/02/2021	1
1	1212	09/06/2021	1
1	1212	09/05/2021	1
1	1212	09/04/2021	0
1	1212	09/03/2021	0
1	1212	09/02/2021	1

So the table with the cumulative calcul (based on today() so change at every load) will be :

mag	cug	Nb Cumulative
1	1010	3
1	1212	2

Thank you 

Have a good day

[chrismarlow · Accepted solution]

Hi,

I would start with joining the last date with zero in flag to the data (so code similar to below) & then you have choices depending what you want to do (you could create a new flag that is only 1 where date>maxdate and sum, you could aggregate that into a separate table ...).

Cheers,

Chris.

data:
load * Inline [
mag, cug, date,flag
1, 1010, 06/09/2021,1
1, 1010, 05/09/2021,1
1, 1010, 04/09/2021,1
1, 1010, 03/09/2021,0
1, 1010, 02/09/2021,1
1, 1212, 06/09/2021,1
1, 1212, 05/09/2021,1
1, 1212, 04/09/2021,0
1, 1212, 03/09/2021,0
1, 1212, 02/09/2021,1
];

maxzero:
Load
	mag,
	cug,
	max(date) as maxdate
resident data
where flag=0
group by mag,
	cug;

left join (data)
Load *
resident maxzero;

drop table maxzero;

[QuentinDeci]

Hey Chris,

Thank you very much for your quicky answer !

Sometimes i search difficult solution (whith previous() or while...) but the simplicity is the most time the best!

Have a good day

[chrismarlow]

Thanks, glad it helped.