Aggr & Set analysis ?

[fks]

Hi,

I have the following sample data:

Department	Account	Object	Code	Sum(Amount)
410	6300	1000	200	6 800
410	6500	1000	300	4 225
410	6600	1000	500	225
420	7200		200	77 425
430	6100		800	638 990
451	3500	2000	200	-8 200
451	6100	2000	800	2 200
451	6200	2000	800	3 500
451	3500	8000	400	-7 200
451	6100	8000	800	4 700
451	6200	8000	800	3 400
452	3500	3000	200	-6 000
452	6100	3000	800	4 000
452	6200	3000	800	2 000
452	3500	6000	400	-4 000
452	6100	6000	800	3 000
452	6200	6000	800	1 500
453	3500	4000	200	-40 100
453	6100	4000	800	48 800
453	6600	4000	400	1 000
454	3600	5000	200	-5 050
454	6100	5000	800	4 100
454	6200	5000	800	450
860	7100		800	3 000
860	6100	2000	100	8 200
864	6100	3000	100	6 000
876	6200	4000	100	40 100
889	6100	5000	100	5 050
890	6200		100	7 500

I want to create a table of all Accounts that meets the following criteria (using wildcards):

Department = 45*

Account = 3*

Code = 200

Department	Account	Object	Code	Sum(Amount)
451	3500	2000	200	-8 200
451	6100	2000	800	2 200
451	6200	2000	800	3 500
452	3500	3000	200	-6 000
452	6100	3000	800	4 000
452	6200	3000	800	2 000
453	3500	4000	200	-40 100
453	6100	4000	800	48 800
453	6600	4000	400	1 000
454	3600	5000	200	-5 050
454	6100	5000	800	4 100
454	6200	5000	800	450

I recon it's kind of a two-step process; first step to find the Objects related to Code 200 (where Department = 45* and Account = 3*) and then display the results where Department = 45*.

I tried to find a smooth solution with the Aggr function & Set analysis but haven't managed so far. So all help very much appreciated!

(I attach the sample data .qvd)

[lironbaram]

hi 

try this expression 

Sum({<Department={"45*"},Code={"200"},Account={"3*"}>}Amount)

[sunny_talwar · Accepted solution]

Try this

Sum({<Key = p({<Department = {"45*"}, Account = {"3*"}, Code = {"200"}>})>}Amount)

Where Key is a field created in the script like this

[SALES]:
LOAD *,
	 Department&Object as Key;
LOAD * INLINE [
	Department, Account, Object, Code, Amount     
    410, 6500, 1000, 300, 4225
    410, 6300, 1000, 200, 6800
    410, 6600, 1000, 500, 225
    420, 7200, , 200, 77425
    430, 6100, , 800, 638990
    451, 3500, 2000, 200, -8200
    451, 6100, 2000, 800, 2200
    451, 6200, 2000, 800, 3500
    451, 3500, 8000, 400, -7200
    451, 6100, 8000, 800, 4700
    451, 6200, 8000, 800, 3400
    452, 3500, 3000, 200, -6000
    452, 6100, 3000, 800, 4000
    452, 6200, 3000, 800, 2000
    452, 3500, 6000, 400, -4000
    452, 6100, 6000, 800, 3000
    452, 6200, 6000, 800, 1500
    453, 3500, 4000, 200, -40100
    453, 6100, 4000, 800, 42000
    453, 6600, 4000, 400, 1000
    453, 6100, 4000, 800, 6800
    454, 3600, 5000, 200, -5050
    454, 6100, 5000, 800, 4100
    454, 6200, 5000, 800, 450
    860, 6100, 2000, 100, 8200
    860, 7100, , 800, 3000
    864, 6100, 3000, 100, 6000
    876, 6200, 4000, 100, 40100
    889, 6100, 5000, 100, 5050
    890, 6200, , 100, 7500
];