Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 :
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 :
Thank you very much, it works! 🙂
Hi,
Can you explain : peek(Name), Name ?
Thanks