Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
My question is probably easy enough and I hope you don't blame my ignorance 🙂
So, let's suppose that we have a simple table with the following data:
INVOICE NO. ITEM AMOUNT
---------------------------------------------------------------------------------------------------
111 AAA 10
111 BBB 15
222 AAA 5
333 CCC 17
333 BBB 4
444 AAA 3
Now I would like to count all invoices which contain AAA items, but do NOT contain BBB items.
I guess that I maybe have to use aggr() here, but I do not quite realize how to do it.
Thanks in advance!
Hi!
No, the data is not in this format, but it could probably be treated this way by concatenation.
However, I find this approach somehow not so elegant, having in mind the rules for relational database design... and I still struggle to find a function to do the necessary filter.
At the end of the day, I will obviously have to try with the concatenation.
Hi
the expression
if (substringcount(aggr(concat(ITEM),INVOICE),'BBB')=0,sum(Amount))
will give you the amount for invoices not containing 'BBB'
Be careful with the string comparison, BBBB will also be considered BBB. You might want to have delimiters at start and end and of concat string and also in the item, e.g.
substringcount( ',AAA,BBB,', ',BBB,')
Juerg
Hello!
Thanks for this suggestion, I would not be able to imagine it myself.
I will give it a try and then I will post back.