topic Show the Max Rows in QlikView

Show the Max Rows

dmohanty — Thu, 04 Feb 2021 16:30:23 GMT

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!

Re: Show the Max Rows

Taoufiq_Zarra — Fri, 05 Feb 2021 09:03:11 GMT

@dmohanty in load Script ?

Re: Show the Max Rows

dmohanty — Fri, 05 Feb 2021 09:13:45 GMT

@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.

Re: Show the Max Rows

dmohanty — Sun, 07 Feb 2021 13:43:00 GMT

@Taoufiq_Zarra ,

Any possible suggestions?

Regards!

Re: Show the Max Rows

Taoufiq_Zarra — Mon, 08 Feb 2021 09:53:01 GMT

@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:

Re: Show the Max Rows

dmohanty — Mon, 08 Feb 2021 17:07:33 GMT

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!

Re: Show the Max Rows

dmohanty — Mon, 05 Apr 2021 11:36:38 GMT

Hi @Taoufiq_Zarra ,

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