Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

filter invoices based on items inside

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!

12 Replies
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.