filter invoices based on items inside

[#1]

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!

[sathishkumar_go]

Hi

use this COUNT(if(Item = 'AAA',Invoice))

-Sathish

[#3]

Your solution seems obvious, but I think that this way I will count invoice 111 and that would be wrong...

[#4]

Hi gmanoilov,

Is the data in the same structure as you habe illustrated? Or is the structure a bit different?

[#5]

Hello!

In fact, the physical tables in the source database are different.

We have a table with the general invoice data (eg. creation date, place of delivery, etc.), then we have another table with the item details (it contains records for all items in all invoices) and we have a third table that contains foreign keys to link the first two tables.

But is this really important?

[#6]

Hi

Can you load a concatenated string of items for each invoice and then rule invoices out that contain your unwanted items?

Not sure about your requirements but can you use the items count in the invoice and rule out the ones having more than one item?

Regards

Juerg

[#7]

What I wanted to confirm was whether all the items are stated against an Invoice

eg INVOICE # 111 would have AAA & BBB both written in front of it?

If that is the case then you can use Index function to find the location of required names

[#8]

Unfortunately I cannot just rule out invoices with more that one item, because I can have invoices with items AAA and CCC (and even DDD and etc.) and they should still be taken into account.

Maybe the concatenation idea would work, but I will be much more happy if it could be done with a simple combination of aggregation and conditional functions in the chart...

[#9]

Hello Umang!

I am not sure what you mean by "written in front of it", but yes, all items are linked to an invoice using primary and foreign keys in the database. I do not know much about the index function, so I will go and read. (I am not very optimistic, though) Then I will post back my comments.

Best wishes!

[#10]

Hi,

What I mean to ask is whether your data is in this format ( or can be brought into this format.

INVOICE	ITEM	AMOUNT
111	AAA,BBB	18
222	AAA	28
333	CCC,BBB	13
444	AAA	32