Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggr() function (merging values in table)

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try

SUM( AGGR(

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

,Material, Order))

View solution in original post

10 Replies
vishsaggi
Champion III
Champion III

Hi Alexander,

Is this what you are looking for:

AggrData.png

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

V.

swuehl
MVP
MVP

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?

Not applicable
Author

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
Specialist III
Specialist III

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

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

Not applicable
Author

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.

Not applicable
Author

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
MVP
MVP

Try

SUM( AGGR(

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

,Material, Order))

ramoncova06
Specialist III
Specialist III

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
Champion III
Champion III

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.