Pick aggregated value using Aggregate component and use it as a new expression
I am trying to take max value of a column based on an aggregated column and apply it as a new column (static value) across all records excluding aggregation.
I tried using Aggregate component. But I see that we can only output the columns in selection. So basically all other fields cannot go through this component.
I tried creating a new column using Expression component by writing a case statement. However, the value in this column is only applied to aggregated column value.
Is there a way I can calculate an aggregate value using Expression and use it as a static value across all records ?
4 Community Answers
Damian Chan —
Hopefully, I’ve understood this correctly.
Have you had a look at the Window Calculation component? You should be able to use it do a MAX on your original column and assign the value to a new column, therefore, retaining your original column in the output.
When I try Window calculation, my data looks like this
Fruit | Number | TotalNum ---------------------------------------------- apple | 10 | 10 pear | 2| null grape | 8 | null banana | 6 | null
How can I get value for apple and apply it across other "Fruit" ?
I tried Aggregator, Expression, Window calculation transformations. All I end up is above result set because obviously I am partitioning the data to get the value. How can I keep this value static and apply it across.
The reason I asked maximum is because, I tried to do a max of the column (TotalNum) and create new column and apply the static max value across all data. I believe this approach may be incorrect.