Count a value of a field between two other values

[ontoit7331]

Hello,

I need to count all the values between two other values.

for example:

This is the rows i wanna read from an excel

Time	Start	Value	Name
07:00	Y		John
08:00		1
09:00		2
10:00		4
11:00	Y		Robert
12:00		2
13:00		2
14:00		10

I want the table to look like this when it's done:

Name	Value
John	7
Robert	14

Is there by any chance  possible to count the values between the Start Time and the next Start Time?

Sorry for my grammar.

Thanks in advance.

Best regards,

Robin

[Taoufiq_Zarra · Accepted solution]

maye be :

Data:

load 

Time,
Start,
Value,
if( len( trim(Name) ) = 0, peek(Name), Name)  as Name
;

LOAD * INLINE [
Time,Start,Value,Name
07:00,Y,,John
08:00,,1,
09:00,,2,
10:00,,4,
11:00,Y,,Robert
12:00,,2,
13:00,,2,
14:00,,10,
];



output:
noconcatenate
load 
Name,
Sum(Value) as Value

resident Data
group by Name;


drop table Data;

 output :

[ontoit7331]

Thank you very much, it works! 🙂

[sergio0592]

Hi,

Can you explain : peek(Name), Name ?

Thanks

[Taoufiq_Zarra]

Hi, to fill the null values with the previous values