Help With Group by from Resident table

[davyqliks]

Hi All,

I need to perform a calculation ( which is working in the model) in the load.

The Data required is from three different tables so i have joined those fields as per the below:

Now using that resident table i try to perform the following calculation 

if(
Sum(([Garment average net weight (gram)] * [Weight Ratio] )/100 * Percentage / 100 ) / 2
/
[Garment average net weight (gram)] * 100
>=50, 'Sustainable','Not Sustainable')

the return i get is different to that in the model and i believe this is due to the Group by Clause.

i only want to group by "Portal Order No.", however when i remove any other dimension from the group by i get Invalid expression in the load.

Is there a way i can make this Expression in the load with only 1 grouping "Portal Order No."

thank you

Daniel

[edwin]

if you want to get the aggregation of specific fields but only over Portal Order No, then you should include only that field. 

this will result in a new table with just 2 fields which should be fine as that is the aggregate over Order No

[davyqliks]

Thanks Edwin for taking the time to assist, when i do that i get the error mentioned:

see below:

thanks

[edwin · Accepted solution]

i see the problem.  it was hard to see the aggregation form the pic.  this is bec the field [Garment ave net weight] is outside of the aggregation.  this means you need to split this up to two steps but you need some analysis prior to doing it.  you asically need to aggregate first and decide if the aggregation is at order no level.

get this first:

then you can either inner join it or use applymap to look it up and incorporate into your main table - prior to doing the if statement:

1. get the aggregated value first

2. when you build you main table, lookup the aggregated value and apply it in your if statement

[edwin]

this is assuming that your analysis will arrive at the fact that the aggregation is only over order no

[edwin]

hope that helps

[davyqliks]

Thanks for your assistance, i see this is the right approach and it has worked to a degree for me to get part 1 correct,

Thanks,

I need to approach this in a different way, reagrds

Daniel

[edwin]

yw