So I have a BAQ that I want to count the number of rows after it’s been grouped, essentially giving me how many different combinations there are. That works fine. Next, for all of the rows that have only one combination, I need the field that I am looking at to be populated. So I’m trying a calculated field that says, if the count equals one, then display the value of the field. If it’s not 1, then display and empty string.
The problem is, if I try to put that field into the calculation, then tells me it can’t use it, because it’s not part of the grouping. But if I include that field, that I can’t count the number of rows with that specific combination, because the field that I am trying to count would make the row unique, and I get 1 for everything. Here’s a basic example.
Raw data
parent child status
A 1 x
A 1 x
A 2 y
B 1 x
B 1 y
grouped by parent and child, counting by unique status
A1 count 1
A2 count 1
B1 count 2
I can get that working fine, now I need to add a column for the unique status when the count is 1
A1 1 status X
A2 1 status y
B1 2 status ‘’
This is where is fails because I have grouping set up for parent and child, and the count as an aggregate, but if I try to do a conditional statement:
(case when CountRows = 1 then GroupingSuggestions.UD08_ShortChar02 else ‘’ end)
Then I get this error.
Severity: Error, Table: , Field: , RowID: , Text: Column ‘GroupingSuggestions.UD08_ShortChar02’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How can I pull that field from the row without it being in the group by clause? I tried basically duplicating the sub queries that make this up in order to lookup the one field, but it’s a union that takes a long time, and it didn’t want to finish.
Any suggestions?