topic Re: Get count from multiple results. in QlikView

Get count from multiple results.

Thu, 27 Nov 2014 14:19:49 GMT

Hi guys ive got a problem, i think its simple but I cant seem to get it working, here is the problem.

I have several Tickets lets say A, B, C, D, and during time the tickets change status so Ticket A has been status 1, 2 and 3, ticket B has been status 2 and 3, Ticket C haben been status 1 and 2 and Ticket D has been status 1 and 3.

Like so;

Ticket	Status
A	1
A	2
A	3
B	2
B	3
C	1
C	2
D	1
D	3

So I want a calculation that counts the number of tickets that have ever been status 1 and the number of tickets that have had any status expect 1.

SO in this case the result would be.

Status 1 Tickets = 3 (A, C, D)

Status non 1 Tickets = 1 (B)

Thanks

Re: Get count from multiple results.

syukyo_zhu — Thu, 27 Nov 2014 14:36:38 GMT

Status 1 Tickets = 3 (A, C, D) : Count(distinct {<Ticket=p({<Status={1}>}Ticket)>}Ticket)

Status non 1 Tickets = 1 (B): Count(distinct {<Ticket=e({<Status={1}>}Ticket)>}Ticket)

Re: Get count from multiple results.

ali_hijazi — Thu, 27 Nov 2014 14:37:54 GMT

I would load your data

then get the min(Status) by Ticket

temp:

Load Ticket, min(Status) as Status

resident your_table

group by Ticket;

temp2:

load * if(status=1, 1,0) as Got_Status_1

resident temp;

drop table temp;

inner join(your_table)

load Ticket, Got_status_1 resident temp2;

drop table temp2;

your expression will be

count(distinct {<Got_Status_1={1}>} Ticket) for those who got 1 at least once

the second would be count(distinct {<Got_Status_1={0}>} Ticket)

Re: Get count from multiple results.

giacomom — Thu, 27 Nov 2014 14:42:06 GMT

Hi Gualter,

you can use set analysis expressions, both to:

1. count the number of tickets that have been status 1:

=Count({$ <Status={1}>} DISTINCT Ticket)

2. count the number of tickets that have been any status but 1:

=Aggr(Count({$ <Status=-{1}>} DISTINCT Ticket), Ticket)

Regards,

Giacomo

Re: Get count from multiple results.

Thu, 27 Nov 2014 14:43:04 GMT

Sorry I should have made a better example, my Status are actual Strings and not numbers, so I cant use Min or Max.

Re: Get count from multiple results.

PradeepReddy — Thu, 27 Nov 2014 14:43:37 GMT

In front end using set analysis...

1) Status 1 Tickets = 3 (A, C, D)

count(distinct {<Status={'1'}>}Ticket)

2) Status non 1 Tickets = 1 (B)

count(distinct Ticket) - count(distinct {<Status={'1'}>}Ticket)

Re: Get count from multiple results.

jerrysvensson — Thu, 27 Nov 2014 15:03:07 GMT

Have you looked at syukyo_ZHU solution(further up)?

Should work perfectly and it is fast.

Re: Get count from multiple results.

ali_hijazi — Thu, 27 Nov 2014 15:08:37 GMT

you can use minstring