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

Announcements
Posting on Qlik Community is temporarily paused for some users. Thank you for patience and we'll update you soon!
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