Conditional sum in set analyse

[jozisvk11]

Hello, i need to help with set analyse. I have table:

ID transaction          Item          Quantity          Value

     10                      M2000         5                    100

     10                      M2000          5                    10

     20                      M2000          20                  200

     20                      M2000          20                  20

     30                      M2000        50                   500

     30                      M2000         50                   50

     40                      M2000         30                  300

I  need to result. (Quantity should be max value according to ID transaction and Value si sum of all rows). I would like to write it in set analyse.   

Result table:

Item          Quantity          Value

M2000         105              1180

Thank you.

[aarkay29]

Try this

quantity=sum(Aggr(Max(Quantity),[ID transaction]))

value= sum(Aggr(Max(Value),[ID transaction]))

[sunny_talwar]

I think value will be Sum(Value) and may be this for Quantity

Sum(Aggr(FirstSortedValue(Quantity, -Value), [ID transaction], Item))

[Anonymous]

... should work, but I would use firstsortedvalue always in combination with distinct or make sure, that the first sorted value is unique...

[sunny_talwar]

I don't like using DISTINCT, because in case there are two options, the FirstSortedValue() will randomly choose one of the two options.... do you want to have that or use another method?

[Anonymous]

it depends

[sunny_talwar]

exactly.... that's why not using DISTINCT here... because I don't know how this is getting used here

[jozisvk11]

My firts set analyse is quite complicated:

sum ({< [ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396"}, [Object] = {'whinr110'}, [Division] ={'701', '702', '703', '723'} >} [Quantity]).  How can I add yours expression in my Set analyse ???

1. Aar - quantity=sum(Aggr(Max(Quantity),[ID transaction]))  or

2. Sunny - Sum(Aggr(FirstSortedValue(Quantity, -Value), [ID transaction], Item))

Can I add it to Set analyse ??

[Kushal_Chawda · Accepted solution]

another way

Data:

LOAD *, AutoNumber([ID transaction]&Item) as Key Inline [

ID transaction,Item,Quantity,Value

     10,M2000,5,100

     10,M2000,5,10

     20,M2000,20,200

     20,M2000,30,20

     30,M2000,50,500

     30,M2000,50,50

     40,M2000,30,300 ];

Left Join(Data)

LOAD Distinct Key,

     max(Quantity) as MaxQuantity

Resident Data

Group by Key;

quantity, sum(aggr(Sum(distinct MaxQuantity),key))

value , Sum(Value)

[sunny_talwar]

Based on private conversation, it seems that this worked for the OP

Sum({<[ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396"}, [Object] = {'whinr110'}, [Division] ={'701', '702', '703', '723'}>} Aggr(FirstSortedValue({<[ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396"}, [Object] = {'whinr110'}, [Division] ={'701', '702', '703', '723'}>} Quantity, -Value), [ID transaction], Item))