Sum... if...don't exist in...

[Anonymous]

For example;

If I have two tables: Orders and Delivery, and Orders consists of Amount and Orderno. In Delivery I have Orderno of those orders that have been shipped. If I now want to sum Orders.amount on orders that haven't been shipped (Ordernumbers that don't exist in the Delivery-table), what should I do?

[richard_chilvers]

Hi

In your delivery table, look at what information you hold about orders which have been shipped (eg. despatch date).

You can then list data from the orders table, where the relevant field (eg. despatch date) doesn't exist (you may need to use a NULL function here).

Hope that gives you some ideas.

Regards

[sunny_talwar · Accepted solution]

You can use a flag in your script to highlight order which are shipped. May be like this:

Order:

LOAD OrderNo,
          Amount

FROM Order;

Shipped:

LOAD OrderNo,

          DateShipped,

          1 as ShippedFlag

FROM Shipped;

Expression would be:

Sum({<ShippedFlag = e({<ShippedFlag = {1}>})>} Amount)

HTH

Best,

S

[maxgro]

I think you can use 2 listbox, one from Delivery table (not Orderno) and one from Orders (Orderno)

select all from the first listbox

then select excluded from the second (Orderno)

Finding NULL

[Anonymous]

Thank you all! I decided to use Sunindia's Flag.