Aggr() function (merging values in table)

[#1]

Hello,

I am trying to merge rows from a table that all have the same values in one of the columns.

Example:

Material, Order, # of Changes

4456,210412,0

4456,201443,3

4406,123456,5

4406,203034,2

4456,203530.1

The result I want is:

Material, Order, # of Changes

4456,(this column will be hidden),4

4406,(""),7

I just want to see the # of changes for each material, but I have to include the order somehow or else the # of changes is a ridiculously high number. Any help would be lovely!

Thank you.

[vishsaggi]

Hi Alexander,

Is this what you are looking for:

Use the straight table and use Material as Dimension and Sum(# Changes) as expression.

V.

[swuehl]

Why should the number of changes be ridiculous high? Do you have duplicate lines that you need to take care of somehow?

Or is your request already fulfilled by what vishsaggi suggested?

[#4]

Hi,

It says there is an error when I do that. The # of Changes is an expression: if((count(distinct([Promise Date]))-1)>=0,(count(distinct([Promise Date]))-1),'-')

Right now I have Material and Order as dimensions and # of Changes as expression.

[ramoncova06]

use total <> that way you do not have to include the order field to the table

sum(total <Material> [# of Changes])

[#6]

Hi,

I just replied to vishsaggi's response, but my expression for # of Changes is if((count(distinct([Promise Date]))-1)>=0,(count(distinct([Promise Date]))-1),'-')

I am counting the number of changed dates for each order, which ends up being around 4000 for my data set, but when I do just materials and number of date changes, it comes out to over 50000.

[#7]

Hi,

I tried that but it keeps telling me that nesting aggregation is not allowed. Are you saying to do sum(total <Material> [# of Changes]) with the expressions in place of # of Changes?

[swuehl · Accepted solution]

Try

SUM( AGGR(

if((count(distinct([Promise Date]))-1)>=0,(count(distinct([Promise Date]))-1),'-')

,Material, Order))

[ramoncova06]

I assumed the "# of Changes" was a field, in your case it is better to use an aggr, believe Swuehl, already provided the expression

[vishsaggi]

I agree with Ramon, I thought that is the field. I believe what Swuehl expression should give you what you are looking for. If you still have issues please upload your qvw sample will be easy to work on it quickly.

V.