Using AGGR in list boxes

[pauljohansson]

Hi,

Do you have a good understandning of the AGGR function and listboxes. Continue to read

Ok, lets start with a clear description of the case.

My table structure:

Date | TransType | Amount | Customer

The interface: I have two list boxes using AGGR expressions:

Listbox1

=aggr(sum(IF(TransType = 'Deposit', Amount)), Customer)

Listbox2

=aggr(sum(IF(TransType = 'Withdrawal', Amount)), Customer)

When i make the selection '100'  in Listbox1 I get all the customers that have a deposited the value equal to 100, fine. And of course the same goes when I select 100 in Listbox2.

However, i want to be able to select all the customers that have Deposited exactly 100 OR Withdrawl exactly 100.

If I select 100 in both of the listboxes, I get all the customers that both have deposited 100 AND withdrawald 100.

But want im looking for is this:

Customer | Deposit  | Withdrawl

a                100            0              

b                100            0         

c                100            0              

d                0               100

e                0               100

f                 0               100

One might think that the solution is to create a listbox based on the following expression:

=aggr(sum(IF(TransType = 'Deposit' or TransType = 'Withdrawal', Amount)), Customer)

This gets close, but its not right, because it leaves us with a result that might look like:

Customer |  Deposit  |     Withdrawl

a                100                  0    

b                100                  0

c                50                    50

d                30                    70

e                 0                    100

f                 0                     100              

Do anyone have any input how to solve this? Or maybe can point in a direction?

Thanks in advance,

br

Paul

[stevedark]

Hi Paul,

If you add the TransType to the aggr parameters does that give the desired result?

=aggr(sum(IF(TransType = 'Deposit' or TransType = 'Withdrawal', Amount)), Customer, TransType)

Personally, I would look at doing the sum in the load script, as it will then perform a lot better at run time - and only cause a small hit at load time.  After loading your main table you could load the aggregate amounts with a resident load like this:

Totals:

LOAD

  Customer,

  TransType,

  sum(Amount) as [Total Amount]

RESIDENT OtherTable

GROUP BY

  Customer,

  TransType

;

This way you can then just have a simple list box on the Total Amount field, and this would then make the selection in the way that you require.

As a rule the more you can do in the load script the better.

Hope that helps,

Steve

http://www.quickintelligence.co.uk/