topic Re: can you help me with two complex queries? in QlikView

can you help me with two complex queries?

marko_rakar — Mon, 03 Feb 2014 09:01:28 GMT

can you help me with two complex queries? (complex at least to me).

Here is the problem (simplified, but this is more or less database I have):

Invoice_header:

Load * INLINE

[

Invoice_ID, Name, Total_amount

1, Zeus, 1000,

2, Thor, 1500,

3, Venus, 2700, ];

Invoice_items:

Load * INLINE

[

Invoice_ID, item_name, Item_price, Item_color, Item_list_price

1, wheel, 500, black, 550

1, seat, 500, black, 550

2, chain, 700, black, 700

2, tire, 800, white, 800 ];

I need to make two queries which will help me find following:

I want to find all invoices where we have mismatch between item colors; idea is to find all invoices which were issued but they have different color defined for items); rule is that all items should have same color (color is not defined in invoice header, although I see that I can find a way to define correct color for each invoice)
I want to find a way to find all invoices where total amount in header is not the same like sum of items list price (I was thinking of creating running totals of items list price somehow but failed to find a proper way)

This is not actual database structure but I can derive data in this way (this structure is derived from multiple databases).

Any ideas, pointers?

thanks

p.s. I know this is not a standard query or even a situation which should happen in the first place, but what I am trying to do is to find if the database was tampered with and I have to reconstruct results and compare it with what was supposed to happen

Re: can you help me with two complex queries?

whiteline — Mon, 03 Feb 2014 09:51:58 GMT

Hi.

Create a chart with Invoice_ID as dimension.

This expression gives you only those having more than one color:

=Count(DISTINCT Item_color)>1

This expression gives you only those having total differ from items:

=Sum(Item_price)<>Total_amount

You can use the same approach and add list boxes with expressions that allow user to select such Invoices.

=aggr(Count(DISTINCT Item_color)>1 , Invoice_ID)

Re: can you help me with two complex queries?

marko_rakar — Mon, 03 Feb 2014 12:21:54 GMT

quite simple (and when I look at that now, quite obvious) solution

it works like a charm, now I have to test it on much more complex formulas

but, can someone tell me why this formula works as expression and not as calculated dimension? (it should behave the same, or at least that is what I understand)

Re: can you help me with two complex queries?

whiteline — Mon, 03 Feb 2014 18:48:35 GMT

Hi.

Actually, it works. But you should specify, which field you want to use as aggregation.

Try the third expression as a calculated dimension.