topic Re: Count a value of a field between two other values in QlikView

Count a value of a field between two other values

ontoit7331 — Tue, 21 Jan 2020 13:57:35 GMT

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

Re: Count a value of a field between two other values

Taoufiq_Zarra — Tue, 21 Jan 2020 21:18:14 GMT

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 :

Re: Count a value of a field between two other values

ontoit7331 — Wed, 22 Jan 2020 08:13:38 GMT

Thank you very much, it works! 🙂

Re: Count a value of a field between two other values

sergio0592 — Wed, 22 Jan 2020 13:45:52 GMT

Hi,

Can you explain : peek(Name), Name ?

Thanks

Re: Count a value of a field between two other values

Taoufiq_Zarra — Wed, 22 Jan 2020 13:50:14 GMT

Hi, to fill the null values with the previous values