topic Re: How to get a count of field2 for each field1 basedo in QlikView

How to get a count of field2 for each field1 basedo

Wed, 25 Nov 2020 16:16:04 GMT

Hello users, I am new to the community and QV usage. Struggling how to accomplish it in Qlikview Here is how my data is and the result I want to produce. Appreciate the help. I tried the count if distinct option, but it is not working for some reason. As you can see from the data (left three columns), I want to product the results (right 2 columns). In my result I do not need to show the states, but just the count of states for each month, where the calls are over 25.

Thank you.

Data: Result

Month	State	Calls
Jan	AK	25	Month	Count of States with >25 calls
Jan	AL	27	Jan	2
Jan	IL	15	Feb	2
Feb	AK	14	March	5
Feb	AL	50	April	4
Feb	MS	200
Feb	TN	10
March	NJ	9
March	NY	150
March	TX	25
March	TN	24
March	NM	33
March	UT	90
March	SD	56
April	NJ	45
April	NY	55
April	TX	83
April	TN	31
April	NM	12

Re: How to get a count of field2 for each field1 basedo

Anil_Babu_Samineni — Fri, 03 Mar 2017 08:58:55 GMT

Take Straight Table

Dim as Month

Expression would be -- Count({<Calls = {'>25'}>}State)

Re: How to get a count of field2 for each field1 basedo

devarasu07 — Fri, 03 Mar 2017 09:09:50 GMT

Hi,

In your expression,

Count({<Calls = {'>=25'}>}State)

Hope this helps

Deva

Re: How to get a count of field2 for each field1 basedo

daniel_kusiak — Fri, 03 Mar 2017 09:14:11 GMT

Hi, please check the attached file.

Re: How to get a count of field2 for each field1 basedo

Anonymous — Fri, 03 Mar 2017 10:07:33 GMT

Is there a way , where we can get the desired data directly from scripting instead of showing up in front end.

I meant can we see the desired output in Table viewer . with the help of scripting...

Re: How to get a count of field2 for each field1 basedo

Anonymous — Fri, 03 Mar 2017 10:14:32 GMT

Use this in

Re: How to get a count of field2 for each field1 basedo

Anonymous — Fri, 03 Mar 2017 10:14:47 GMT

T1:

LOAD
     Month,
     State,
     Calls

FROM

(ooxml, embedded labels, table is Sheet1)
Where Calls>=25;

T2:

LOAD
     Month,
     Count(State)
     Resident T1 Group by Month;


Drop Table T1;

Re: How to get a count of field2 for each field1 basedo

Fri, 03 Mar 2017 15:33:34 GMT

Dan, Thank you. I do not see the attachment.

Re: How to get a count of field2 for each field1 basedo

Fri, 03 Mar 2017 15:42:47 GMT

Thank you for your responses, the expression is working. I just didn't realize my data has one more segment within State. Here is how it looks:

1) So the calls total has to be first aggregated to State level from all Zones, then the state sum has to be checked against 25.

2) The result should still display all 12 months, even if there are months that do not have any states with > 25, it should still show that month, with a blank for state count

Thank you all.

Month	State	Zone	Calls
Jan	AK	1	10
Jan	AK	2	10
Jan	AK	3	5
Jan	AL	1	9
Jan	AL	2	9
Jan	AL	3	9
Jan	IL	3	15
Feb	AK	1	5
Feb	AK	2	5
Feb	AK	3	5
Feb	AL	2	50
Feb	MS	3	200
Feb	TN	2	10

Re: How to get a count of field2 for each field1 basedo

Fri, 03 Mar 2017 22:43:02 GMT

Thank you for your responses, the expression is working. I just didn't realize my data has one more segment within State. Here is how it looks:

1) So the calls total has to be first aggregated to State level from all Zones, then the state sum has to be checked against 25.

2) The result should still display all 12 months, even if there are months that do not have any states with > 25, it should still show that month, with a blank for state count

Thank you all.

Month	State	Zone	Calls
Jan	AK	1	10
Jan	AK	2	10
Jan	AK	3	5
Jan	AL	1	9
Jan	AL	2	9
Jan	AL	3	9
Jan	IL	3	15
Feb	AK	1	5
Feb	AK	2	5
Feb	AK	3	5
Feb	AL	2	50
Feb	MS	3	200
Feb	TN	2	10

Re: How to get a count of field2 for each field1 basedo

Fri, 03 Mar 2017 22:43:14 GMT

Thank you for your responses, the expression is working. I just didn't realize my data has one more segment within State. Here is how it looks:

1) So the calls total has to be first aggregated to State level from all Zones, then the state sum has to be checked against 25.

2) The result should still display all 12 months, even if there are months that do not have any states with > 25, it should still show that month, with a blank for state count

Thank you all.

Month	State	Zone	Calls
Jan	AK	1	10
Jan	AK	2	10
Jan	AK	3	5
Jan	AL	1	9
Jan	AL	2	9
Jan	AL	3	9
Jan	IL	3	15
Feb	AK	1	5
Feb	AK	2	5
Feb	AK	3	5
Feb	AL	2	50
Feb	MS	3	200
Feb	TN	2	10

Re: How to get a count of field2 for each field1 basedo

Sat, 04 Mar 2017 03:17:49 GMT

Thank you Dan, the expression is working. Now I got your attachment, sorry I didn't see it first, not sure if moderator has to first approve it. I just didn't realize my data has one more segment within State. Here is how it looks:

1) So the calls total has to be first aggregated to State level from all Zones, then the state sum has to be checked against 25.

2) The result should still display all 12 months, even if there are months that do not have any states with > 25, it should still show that month, with a blank for state count

Thank you!

Month	State	Zone	Calls
Jan	AK	1	10
Jan	AK	2	10
Jan	AK	3	5
Jan	AL	1	9
Jan	AL	2	9
Jan	AL	3	9
Jan	IL	3	15
Feb	AK	1	5
Feb	AK	2	5
Feb	AK	3	5
Feb	AL	2	50
Feb	MS	3	200
Feb	TN	2	10

Re: How to get a count of field2 for each field1 basedo

Sun, 05 Mar 2017 18:43:54 GMT

Dear Users, I posted a further query on this as the data has one more dimension, but not sure if I posted it the correct way so that who all responded with answers see the updated question. Do I have to post it as a new query, as I already marked my earlier question as "Answered" and then I re-posted with additional details?

Thank you.

Re: How to get a count of field2 for each field1 basedo

Sun, 05 Mar 2017 18:44:31 GMT

I just didn't realize my data has one more segment within State. Here is how it looks:

1) So the calls total has to be first aggregated to State level from all Zones, then the state sum has to be checked against 25.

2) The result should still display all 12 months, even if there are months that do not have any states with > 25, it should still show that month, with a blank for state count

Thank you!

Month	State	Zone	Calls
Jan	AK	1	10
Jan	AK	2	10
Jan	AK	3	5
Jan	AL	1	9
Jan	AL	2	9
Jan	AL	3	9
Jan	IL	3	15
Feb	AK	1	5
Feb	AK	2	5
Feb	AK	3	5
Feb	AL	2	50
Feb	MS	3	200
Feb	TN	2	10