Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a Excel data Sheet like This
Wagon | Date | Status |
A | 01/01/2021 | In Travel |
A | 02/01/2021 | Stopped |
A | 03/01/2021 | Stopped |
A | 04/01/2021 | Stopped |
A | 05/01/2021 | In Travel |
A | 06/01/2021 | Stopped |
A | 07/01/2021 | Stopped |
A | 08/01/2021 | In Travel |
A | 09/01/2021 | Stopped |
A | 10/01/2021 | In travel |
A | 11/01/2021 | In travel |
Now I want to calculate the number of occurrences of Stops which is (3) but not the count of Stops which is (6)
Thanks in advance
Hi
Add in your script a flag :
somethink like that :
if(match(Status,'Stopped'), if(Status<>previous(Status),1,0)) as count,
than in your app as mesure :
Sum( count)
see below example :
[Table]:
[Table]:
load *,
if(match(Status,'Stopped'), if(Status<>previous(Status),1,0)) as count;
LOAD * INLINE
[
Wagon,Date,Status
A,01/01/2021,In Travel
A,02/01/2021,Stopped
A,03/01/2021,Stopped
A,04/01/2021,Stopped
A,05/01/2021,In Travel
A,06/01/2021,Stopped
A,07/01/2021,Stopped
A,08/01/2021,In Travel
A,09/01/2021,Stopped
A,10/01/2021,In travel
A,11/01/2021,In travel
](delimiter is ',');
Thanks
I didnt test you solution yet
but there might be a B or C wagons between As
Hi
then as mesure use aggr function
agrr(sum(count),wagon) to group stopped occurence per wagon