Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

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

Matillion Agent  

Damian Chan —

Hello Chandana,

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.

https://redshiftsupport.matillion.com/customer/portal/articles/1991935?b_id=8915

Best Regards,
Damian


Chandana M —

Hi Damian,

Thanks. But we need a partition column to use Window calculation. What I am trying to do is
lets say I have below data

Fruit | Number
----------------------------------
apple | 10
pear | 2
grape | 8
banana | 6


I am trying to get the below result:

Fruit | Number | TotalNum
-------------------------------------------------------
apple | 10 | 10 <------ group by at apple
pear | 2 | 10
grape | 8 | 10
banana | 6 | 10


Chandana M —

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.

Any help is greatly appreciated !!!


Chandana M —

Hey Damian,

I found a way by splitting my data set and then joining. Its resolved !

Thanks

Post Your Community Answer

To add an answer please login