Show the Max Rows

[dmohanty]

Hi Experts,

Could you please help me with this QlikView requirement? Below is a sample dataset:

• I need to show the first Timestamp, where the CPU > 90%.
• It should also show the Duration in Seconds till when it remained above 90% .
• When the CPU goes below 90, another Row will be displayed the next time CPU again goes above 90%.

Sample Dataset and Expected Outcome is provided below:

Sample Dataset:

Tab:
Load * Inline
[
Time , CPU
12:00:01, 70%
12:00:02, 92%
12:00:03, 93%
12:00:04, 91%
12:00:05, 54%
12:00:06, 86%
12:00:07, 97%
12:00:08, 95%
12:00:09, 67%
12:00:10, 99%
];

Expected Outcome:

Time	CPU	Duration stayed above 90%
12:00:02	92%	3 secs
12:00:07	97%	2 secs
12:00:10	99%

Appreciate any help! 🙂 

Regards!

[Taoufiq_Zarra]

@dmohanty  in load Script ?

[dmohanty]

@Taoufiq_Zarra  - Either Load Script or Set Analysis (Chart Level)  is fine. 

Preferably at Chart level (with Set Analysis or Formula), as the data set is already loaded into model through Load Script.

[dmohanty]

@Taoufiq_Zarra ,

Any possible suggestions? 

Regards!

[Taoufiq_Zarra · Accepted solution]

@dmohanty  for load you can use for example :

Tab:


load *,if(peek(CPU)<0.9 and CPU>=0.9,1,0) as PickTmp
,if(peek(CPU)<0.9 and CPU>=0.9,1,if(peek(CPU)>=0.9 and CPU>=0.9,peek(Val)+1))  as Val,
if(rowno()=1,0,if(peek(CPU)<0.9 and CPU>=0.9,peek(ValGlobal),if(peek(CPU)>=0.9 and CPU>=0.9,peek(ValGlobal),peek(ValGlobal)+1)))  as ValGlobal;
Load Time , num(CPU) as CPU Inline
[
Time , CPU
12:00:01, 70%
12:00:02, 92%
12:00:03, 93%
12:00:04, 91%
12:00:05, 54%
12:00:06, 86%
12:00:07, 97%
12:00:08, 95%
12:00:09, 67%
12:00:10, 99%
];
left join load ValGlobal,Max(Val) as [Duration stayed above 90%] resident Tab group by ValGlobal;

output:

load Time ,CPU,if([Duration stayed above 90%]=1,'',[Duration stayed above 90%]&' secs') as [Duration stayed above 90%] resident Tab where PickTmp=1;

drop table Tab;

output:

[dmohanty]

HI @Taoufiq_Zarra ,,

Thank you and appreciate your response here. 

I will give this a try, will understand and revert on this. 

Have a good time. 🙂 

Regards!

[dmohanty]

Hi @Taoufiq_Zarra ,

Thank you for the ideas and help. This suggestion is working fine for me. Accepted it as a solution. 🙂