can you help me with two complex queries?

[marko_rakar]

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

[whiteline · Accepted solution]

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)

[marko_rakar]

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)

[whiteline]

Hi.

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

Try the third expression as a calculated dimension.