topic Re: Aggr() function (merging values in table) in QlikView

Aggr() function (merging values in table)

Mon, 29 Jun 2015 14:49:57 GMT

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.

Re: Aggr() function (merging values in table)

vishsaggi — Mon, 29 Jun 2015 15:34:27 GMT

Hi Alexander,

Is this what you are looking for:

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

Re: Aggr() function (merging values in table)

swuehl — Mon, 29 Jun 2015 15:53:04 GMT

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?

Re: Aggr() function (merging values in table)

Mon, 29 Jun 2015 15:54:25 GMT

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.

Re: Aggr() function (merging values in table)

ramoncova06 — Mon, 29 Jun 2015 15:55:46 GMT

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

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

Re: Aggr() function (merging values in table)

Mon, 29 Jun 2015 15:57:12 GMT

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.

Re: Aggr() function (merging values in table)

Mon, 29 Jun 2015 15:59:26 GMT

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?

Re: Aggr() function (merging values in table)

swuehl — Mon, 29 Jun 2015 16:00:53 GMT

Try

SUM( AGGR(

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

,Material, Order))

Re: Aggr() function (merging values in table)

ramoncova06 — Mon, 29 Jun 2015 16:06:51 GMT

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

Re: Aggr() function (merging values in table)

vishsaggi — Mon, 29 Jun 2015 16:11:23 GMT

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.

Re: Aggr() function (merging values in table)

Mon, 29 Jun 2015 16:19:19 GMT

This worked! Thank you so much for your help! I really appreciate it.