I am trying to count the multiple instances where cell value from column B is found in Column A. Similar to how “countif” in excel works. In attached example, the highlighted field under column B should return value of “3”. I’ve tried count() and sum() functions but can’t seem to get the clauses correct.
Please let me know someone can shed some light on this.
Where is the job number in column A and B coming from? This can probably be done with a summarization to count the data from the other table (assuming A & B are from two different tables).
A and B are coming from 2 different tables(subqueries actually). They are linked together right now by PartNum.
ok, you should be able to change one of the queries to be a summary query, and then create a calculated field that does a “Count” of the records.
I changed the query for data on column A to “issummary” flag, however I am not sure how to create the calculated field for the count. I keep getting below error when checking syntax. I haven’t had experience with the summary feature… .
You can’t use a reserved word for your field name. Try MyCount or something else.
True, thanks. I changed it but that was not the issue. Same error.
What about changing the data type from decimal to integer
Same issue.
Is there an example somewhere on how to use these summarized tables? 
I think we need to back up and figure out what you are trying to do first…
What is the data in table A and what is the data in Table B
and what is it you are trying to show by getting the count of instances in A in reference to B
There seems to be some weird many to many thing going on and that really isn’t good unless you have a clear understanding of what you are doing. There is likely a better solution for what you are doing currently.
1 Like
Carmren, both table A and B are from separate PartDtl tables. A is filtered to JM source type and B is not. I am just trying to find if there is a way where I can count instances of A in reference to B so that I can remove them later.
In this case I am creating a BAQ that will show the affected top level jobs if Purchase materials do not arrive on time. I am doing this by identifying when a material record will go negative in PartDtl data, and then go up 3 levels using subqueries. This “countif” will help me identify the affected Material lines and remove them from my next PartDtl query. This can get very complicated since we only build to stock.
Besides this scenario above, there have been other instances where I wanted to use a “countif” in a BAQ for other reasons, but have not been able to figure it out.
I generally do Case statements when I need a CountIf.
sum(case
when x = x then 1
else 0
end)
3 Likes
It needs to be contained in either an aggregate function or the GROUP BY clause though… I’ve used the ‘over’ clause to partition and order items before, but can’t figure out what clause to use here.
That is why I put the SUM before it. Am I missing something?
Can you not group by partnum?
I had nothing grouped. Even if I did group by that column, I would keep getting error until all columns are grouped. Once I group all columns, I get this error instead:
You might be too far down the road for anyone to be able to help you. Not knowing what you have done to date seems to be an issue for any suggestions as you keep getting errors. You might need to go back to the drawing board as it seems like you have a lot going on in your query.
As a side note, I strongly recommend using CTE queries as you can build your own in memory table to join to in another query. Sometimes breaking things down to the lowest level allows you to build back up.
1 Like
edit
I re-read your question, and to do that you need a subquery then join the count to the top level.
This is what I posted before, but it probably isn’t relevant.
I think you can just do this. (you need to uncheck the group by boxes in the field selection)
Count(1) over (partition by field1, field2, etc)
This will do the “grouping” without actually grouping the rows.
2 Likes
Banderson, you are correct. Thank you. I created a subquery to count JobNum(columnA) instances and then joined them on a top lvl by JobNum to the table with Column B data. I was trying to avoid having to do this workaround, but appears that this can’t be done in the calculated field editor alone.