Hi,
I don’t quite appreciate how COUNT() works?
I thought the expression below would yield 2 since there are only 2 rows that match the criteria.
Instead, I get 27… though that result is only in the two expected rows and the other rows are 0 as I would expect…?
Is there something I can read to help me understand this table/row/count concept?
Cheers…
COUNT(
SELECT(
survey[survey_branch_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
OR(
([_THISROW].[select_asset_type] = “Cabinet”),
([_THISROW].[select_asset_type] = “Distribution Board”),
([_THISROW].[select_asset_type] = “FOSE”),
([_THISROW].[select_asset_type] = “HPR”),
([_THISROW].[select_asset_type] = “LPR”)
))))
Solved! Go to Solution.
I think it’s your OR() statement. _THISROW=“XXX” will always return true since its based on the current row not the row that is in the select statement.
Try your select statement without the count() to see what it is returning. It is likely something to do with how the select statement is being filtered not quite matching up.
Hi @Austin_Lambeth,
Thanks… I hadn’t thought of trying that…
SELECT(
survey[survey_branch_number],
AND(
[_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],
[_THISROW].[LCS] = [lcs],
OR(
[_THISROW].[select_asset_type] = “Cabinet”,
[_THISROW].[select_asset_type] = “Distribution Board”,
[_THISROW].[select_asset_type] = “FOSE”,
[_THISROW].[select_asset_type] = “HPR”,
[_THISROW].[select_asset_type] = “LPR”
)))
Here’s why I’m getting 27… I’m not sure if it’s the total rows = 27 or the sum of the 25 items it found = 27?
Should I be writing the expression differently to only return a “1” for each row that it finds that match the criteria…?
Try this:
SELECT(
survey[_ROWNUMBER],
AND(
[_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],
[_THISROW].[LCS] = [lcs],
IN(
[_THISROW].[select_asset_type],
LIST(
“Cabinet”,
“Distribution Board”,
“FOSE”,
“HPR”,
“LPR”
)
)
)
)
Then wrap it in COUNT(),
Hi @Steve,
Thanks for that… Using IN() and LIST() instead of OR() is a new trick for me… Cheers…
COUNT(
SELECT(
survey[_ROWNUMBER],
AND(
[_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],
[_THISROW].[LCS] = [lcs],
IN(
[select_asset_type],
LIST(
“Cabinet”,
“Distribution Board”,
“FOSE”,
“HPR”,
“LPR”
)
)
)
)
)
Please share the magic trick you use to present your expressions so nicely formatted in your posts…?
Precede the formatted text with a line consisting only of three consecutive backticks (```), then follow it with an identical line.
Thank
You
For
That...
Very
Cool...!!!
I think it’s your OR() statement. _THISROW=“XXX” will always return true since its based on the current row not the row that is in the select statement.
Good catch!
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |