27
Views
3
Solved
I nedd help with aggregate
Question
Application Type
Reactive

Hello I am newbie i want to thank you in advance. I need help with these. I have Medicine and transaction aggregate. Join Medicine with or with out Transaction. I use Medicine group of Quantity and Transaction Sum. and i add another attribute with formula Quantity - Sum I named it Stocks. then if there is no transaction The Stocks show 0 instead even i have 900 on Quantity just like the screenshot below 900 - 0 = 0 instead of 900-0 =900

Image Caption

Solution

Hello,

It looks like the problem is due to calculations with NULL values on your right table (Transaction). What you can do to solve this is:

1. Add a new attribute to your aggregate and set its formula to:
If(Transaction.Id = NullIdentifier(), 0, Transaction.Quantity)
2. Sum by this new attribute.
3. Use the new sum in your final "Stocks" calculation.
Glen Gutlay

Hello it worked thank you so much

Solution

Hi Glen,

I am not sure whether I got the entire use-case correctly. Why are you grouping with quantity ?

Do you want to find out total transactions for each medicine ? It will be helpful if you can tell what is your objective and what are the fields you need.

While doing group by in aggregate , if you create a calculated attribute , that attribute won't be available in your final result set. Only the group by values and any aggregate functions(sum/count) will be available in the final result set. You can try with SQL as well.

Solution

Hello,

It looks like the problem is due to calculations with NULL values on your right table (Transaction). What you can do to solve this is:

1. Add a new attribute to your aggregate and set its formula to:
If(Transaction.Id = NullIdentifier(), 0, Transaction.Quantity)
2. Sum by this new attribute.
3. Use the new sum in your final "Stocks" calculation.
Glen Gutlay

Hello it worked thank you so much

Solution

Hi Glen,

I am not sure whether I got the entire use-case correctly. Why are you grouping with quantity ?

Do you want to find out total transactions for each medicine ? It will be helpful if you can tell what is your objective and what are the fields you need.

While doing group by in aggregate , if you create a calculated attribute , that attribute won't be available in your final result set. Only the group by values and any aggregate functions(sum/count) will be available in the final result set. You can try with SQL as well.