Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Want to see what's currently in public preview? We've pulled it all together in one place. Check it out here
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Distinct count query

Hi,

I have the following expression which should count matches from a specified list against four field results. It counts for each match where I want it to count only once if it matches against more than one field result.

COUNT({<
[Accidents Immediate Cause Desc] = {"1d*","1k*","3a*","3b*","3b*","3h*","4s*","6a*","6b*","6g*","6j*","6m*","7m*","7q*","8ab*","8ac*","8ad*","8a*","8b*","8c*","8d*","8e*","8f*","8g*","8h*","8l*","8m*",
"8n*","8o*","8p*","8y*","8z*","9a*","10*","12aa","12e*","12f*","12g*","12n*","12o*","12q*","12r*","12y*","12z*","13*","14a*","14b*","14c*","14d*","14k*","15*"
}>}
distinct [Accidents accidentRef])
+
COUNT({<
[Accidents Underlying Cause Desc] = {"1d*","1k*","3a*","3b*","3b*","3h*","4s*","6a*","6b*","6g*","6j*","6m*","7m*","7q*","8ab*","8ac*","8ad*","8a*","8b*","8c*","8d*","8e*","8f*","8g*","8h*","8l*","8m*",
"8n*","8o*","8p*","8y*","8z*","9a*","10*","12aa","12e*","12f*","12g*","12n*","12o*","12q*","12r*","12y*","12z*","13*","14a*","14b*","14c*","14d*","14k*","15*"}>}
distinct [Accidents accidentRef])
+
COUNT ({<
[Accidents Severity] = {"Moderate","Major"}>} distinct [Accidents accidentRef])
+
COUNT ({<
[Accidents RIDReportable] = {"1"}>} distinct [Accidents accidentRef]

)

so if all four fields match, it counts as 4 where as I want it to count as 1.

I guess the answer is obvious but I cannot get it to work if I combine into one count statement  - is there a max length permitted in QlikView?

Labels (1)
0 Replies