topic Re: Count in Expression in QlikView

Count in Expression

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

please help to solve the issue:

I need to count students with school = home_school only.

Statement : =COUNT({<SCHOOL = HOME_SCHOOL>} DISTINCT [STUDENT NUMBER])

does not give any results.

If I hard code it works

Thanks,

Re: Count in Expression

sunny_talwar — Mon, 09 May 2016 17:00:46 GMT

Can you try this may be:

=COUNT({<[STUDENT NUMBER] = {"=SCHOOL = HOME_SCHOOL"}>} DISTINCT [STUDENT NUMBER])

Re: Count in Expression

Mon, 09 May 2016 17:18:55 GMT

Hi Sunny,

your statement returns the same result as COUNT(DISTINCT [Student Number]), means it doesn't work.

This one works: =COUNT(DISTINCT if (HOME_SCHOOL = SCHOOL,[Student Number]))

What are the rules? Where to learn the syntax from?

Thanks,

Re: Count in Expression

Kushal_Chawda — Mon, 09 May 2016 17:28:44 GMT

try this

=COUNT({<SCHOOL = {"=SCHOOL = HOME_SCHOOL"}>} DISTINCT [STUDENT NUMBER])

Re: Count in Expression

Mon, 09 May 2016 17:35:11 GMT

doesn't work.

This one works: =COUNT(DISTINCT if (HOME_SCHOOL = SCHOOL,[Student Number]))

thanks

Re: Count in Expression

sunny_talwar — Mon, 09 May 2016 18:14:02 GMT

Although, I am not going to force you to reconsider, but just so you know the if statement is not performance friendly. The reason the above expression might not have worked for you is because you might have copy pasted my expression. Your field name is Student Number and not STUDENT NUMBER (which you gave in your initial post). But if you try this, it might work for you.

=COUNT({<[Student Number]= {"=SCHOOL = HOME_SCHOOL"}>} DISTINCT [Student Number])

QlikView is case sensitive and those two fields are read and understood differently by qlikview.

I am attaching a sample for you to look at.

Best,

Sunny

Re: Count in Expression

Mon, 09 May 2016 18:29:46 GMT

Thanks Sunny, I am going to consider your new statement and your advise.

The prev one didn't work because it is not the same and I did change STUDENT NUMBER to Student Number.

Old: =COUNT({<SCHOOL = {"=SCHOOL = HOME_SCHOOL"}>} DISTINCT [STUDENT NUMBER])

New: =COUNT({<[Student Number]= {"=SCHOOL = HOME_SCHOOL"}>} DISTINCT [Student Number])

Please advise on where to read/learn about scripting rules?

Thanks again!!!

Re: Count in Expression

Mon, 09 May 2016 18:32:22 GMT

please explain your solution:

=COUNT({<[Student Number]= {"=SCHOOL = HOME_SCHOOL"}>} DISTINCT [Student Number])

Thanks,

Re: Count in Expression

sunny_talwar — Mon, 09 May 2016 18:38:00 GMT

I did have Student Number

But that is beside the point.

In my expression, I am checking for each Student Number only include those where School = Home_School. It is like a search string on the Student Number field. The key here is that the field you are searching on should be unique for the search you are trying to do. If for a reason you have 2 student numbers where once it matches and other time it doesn't , then the above expression won't work. Example for where it won't work

Student Number, SCHOOL, HOME_SCHOOL

10, 2522, 2522

10, 2865, 2522

Above we see same Student Number associated with two combination. This won't work now.

Does this make sense

Re: Count in Expression

Mon, 09 May 2016 18:57:56 GMT

Thanks again,

It does make sense and it wasn't your statement I was referring to.

Where to learn the syntax from?

Please advise.

Re: Count in Expression

Qrishna — Mon, 09 May 2016 19:13:15 GMT

This should do, in your scenario:

=count({$<HOME_SCHOOL = p(SCHOOL)>} [Student Number])

Re: Count in Expression

sunny_talwar — Mon, 09 May 2016 19:15:41 GMT

Check this out. Added a new row of data

Table:

LOAD * Inline [

Student Number, HOME_SCHOOL, SCHOOL

10417, 2522, 2522

78894, 2856, 2522

91282, 2522, 2522

23133, 2522, 2522

78256, 2522, 2522

46204, 2522, 2522

55106, 2522, 2522

26372, 2522, 2856

];

The output should still remain 6, I believe

Re: Count in Expression

sunny_talwar — Mon, 09 May 2016 19:20:12 GMT

Apologize

You can learn some set analysis from the attached. But more advance stuff can be learned as you play around with it and try to help others on the community.

Best,

Sunny

Re: Count in Expression

Mon, 09 May 2016 19:51:50 GMT

the business rule might help:

Every student can have only one HOME_SCHOOL but many SCHOOLs (enrolment school)

Every SCHOOL counts students only if HOME_SCHOOL = SCHOOL

Re: Count in Expression

sunny_talwar — Mon, 09 May 2016 19:59:58 GMT

If a student can have a multiple SCHOOLs, you need to create a new key, Something like this:

AutoNumber([Student Number]&HOME_SCHOOL&SCHOOL) as Key

and use this as your expression:

=COUNT({<Key= {"=SCHOOL = HOME_SCHOOL"}>} DISTINCT [Student Number])

Re: Count in Expression

Tue, 10 May 2016 13:35:06 GMT

Thank you Sunny for your time!

Re: Count in Expression

sunny_talwar — Tue, 10 May 2016 13:37:20 GMT

Did it final work out for you?

Also, I would ask you to mark any helpful answers.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Count in Expression

Tue, 10 May 2016 14:40:05 GMT

It did work out for me.

Thanks for the Marking Replies reminder.

Re: Count in Expression

sunny_talwar — Tue, 10 May 2016 14:42:30 GMT

I am glad it finally did

Best,

Sunny