topic Re: Data modelling Issue in QlikView

Data modelling Issue

Pulkit_Thukral — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a requirement that from all the occurences of a Server Patching status i need to consider that server as compliant ,if 80 % (or more) of the occurences are have patching status field as success.

Ex:

Server name Date Patch Patching status

xyz may-2016 Antivirus Patch Success

xyz apr-2016 Windows Patch Success

xyz mar-2016 123 Patch Success

xyz feb-2016 345 Patch Failure

abc apr-2016 Windows Patch Success

abc mar-2016 Windows Patch Success

abc may-2016 Windows Patch Success

abc may-2016 Windows Patch Failure

In the above sample data, server "xyz" has in total 4 occurences based on different patches out of which 3 are "Success" .

Hence, success rate is 3/4 =75% means server compliant critiria (80% is not met).

Similarly , for server "abc",total occurences are 5 out of which 4 are success .Hence, this server can be considered as compliant(80 % success rate)

So, to count total servers which are compliant (80% or more success), we should will get below details

Server name Patching status

xyz Failure

abc Success

How can i achieve this in modelling(Scripting part) ?

Re: Data modelling Issue

settu_periasamy — Thu, 19 May 2016 08:14:05 GMT

May be like this..

T1:
Load *,if([Patching status]='Success',1,0) as tmp_field;
LOAD * INLINE [
Server name, Date, Patch, Patching status
xyz, May-16, Antivirus Patch, Success
xyz, Apr-16, Windows Patch, Success
xyz, Mar-16, 123 Patch, Success
xyz, Feb-16, 345 Patch, Failure
abc, Apr-16, Windows Patch, Success
abc, Mar-16, Windows Patch, Success
abc, Mar-16, Windows Patch, Success
abc, May-16, Windows Patch, Success
abc, May-16, Windows Patch, Failure
];

NoConcatenate
T2:
LOAD [Server name],if(Sum(tmp_field)/Count([Patching status])>=0.8,'Success','Failure') as Patching_Status
Resident T1 Group by [Server name];

DROP Table T1;

Re: Data modelling Issue

Pulkit_Thukral — Thu, 19 May 2016 11:04:16 GMT

Thanks for your reply.

I tried using the same and it was working but incorrect values were seen .

Hence, doing it now at UI level only rather than modelling level.