topic filter invoices based on items inside in QlikView

filter invoices based on items inside

Tue, 04 May 2010 08:12:00 GMT

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!

filter invoices based on items inside

sathishkumar_go — Tue, 04 May 2010 08:38:47 GMT

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

-Sathish

filter invoices based on items inside

Tue, 04 May 2010 08:44:36 GMT

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

filter invoices based on items inside

Tue, 04 May 2010 09:23:25 GMT

Hi gmanoilov,

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

filter invoices based on items inside

Tue, 04 May 2010 09:31:41 GMT

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?

filter invoices based on items inside

Tue, 04 May 2010 10:11:54 GMT

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

filter invoices based on items inside

Tue, 04 May 2010 10:17:13 GMT

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

filter invoices based on items inside

Tue, 04 May 2010 10:19:22 GMT

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...

filter invoices based on items inside

Tue, 04 May 2010 10:22:56 GMT

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!

filter invoices based on items inside

Tue, 04 May 2010 11:04:31 GMT

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

filter invoices based on items inside

Tue, 04 May 2010 11:08:02 GMT

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.

filter invoices based on items inside

Tue, 04 May 2010 13:25:53 GMT

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

filter invoices based on items inside

Wed, 05 May 2010 10:11:18 GMT

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.