Aggr Sum() for Aggr Max()?

[jakeng]

Hi All,

I have a table with three columns; ID, category and value.

ID & category tuple pairs are duplicated, i.e. I cannot do a simple sum grouped by ID.

Therefore I would like to take the max for each pair of ID & category and sum these maxima for each ID.

So I would have Aggr(Max(value), ID, Category) and Aggr(Sum(...), ID).

Combining these two expressions into one does not work, i.e. it returns an error message saying the expression is invalid.

Can you please help?

Jakob

[Steven35]

Hi,

If I understand correctly, you want to sum the maximum of the pairs ID/Category by ID.

With data as an example it could give for the max by Value/Category:

And then if we sum by ID:

I don't understand what you want to do with the Aggr(Sum(...),ID).

It is also possible to do this in the script:

[jakeng]

Hi,

Thanks for your reply. I am able to run the first expression, i.e. find the max for each ID & Category.

=Sum(Aggr(Max(Value),ID,Category))

I am not able to sum these values up.

Can it have something in common with the fact that the table contains many other keys, but the group by / sum is performed on two of them?

[Steven35 · Accepted solution]

I think it's because you are adding a dimension and not a measure.

You have to write the formula on a measure:

The following link explains the difference between dimension and measurement:
https://community.qlik.com/t5/New-to-Qlik-Sense/Dimensions-vs-Measures/td-p/1557790

Best regards

[jakeng]

Thanks, that solved my issue. Apologies for my foolish question.

Maybe you could help me with the original issue I am facing? I encounter duplicates after performing an ApplyMap() operation, which leads me to the quick fix discussed above.

Re: Duplicates using Applymap() - Qlik Community - 2028596

I would appreciate if you could take a look in the thread above, anyhow I will mark your answer as the solution. Thanks!

[Steven35]

Hi,

I'm answering here because this will follow my example.

Instead of making an applyMap, make another table linked by the key where the sums will not be duplicated.

When you sum it up:

Whereas if you do a mapping or a left join, it actually counts the values too many times.

For Question 2:
I need to understand in what case you want to use the formula, because I do not understand. Or send me some data so I can understand.

Aggr(Max(Sum2),ID,Category) creates a virtual table which looks like this:

Then when you Sum(Aggr(Max(Sum2),ID,Category) ) , it does the sum of the column Max(Value).
You don't have to put Sum(Aggr(Sum(Aggr(Max(Sum2),ID,Category) ),ID)).

[jakeng]

Hi Steven,

Thanks. The second question is actually what i asked above, so this is solved.

Why does the applymap assign the values several times? I thought it differs from a left join by it doing the assignment on the first matching value only and then going to the next. Instead it behaves just like a left join in my case.

The problem with your solution is that I may not group by id and category in table 1. By design it is supposed to have several rows (20) for one combination of id and category.

How do I add the sum to the first matching row for each id & category cluster only? I was thinking about using a rowno() by partition and then do a where clause for rowno = 1.

Any ideas?

[Steven35]

Jakeng,

ApplyMap is a function, so this is applied to all rows of the table, and not just the first row of each ID for example.

Or in the final table you use the aggr as above:
Sum(Aggr(Max(Value),ID,Category))

Or you can make something with a flag wich value is 1 when the Value is the max value:

Left Join(Table_A_tmp)
Load
	ID&'|'&Category as Key,
    Max(Value) as Max_ID_Category
Resident Table_A_tmp
Group By ID, Category;

Table_A:
LOAD
	*,
    if(Max_ID_Category=Value,1,0) as Flag_Max_ID_Category
Resident Table_A_tmp;

Drop Table Table_A_tmp;

 Then in the set Analysis:

But there must not be 2 equal lines with the max, otherwise they will count twice.
Here it will be ok :

Here there might be some duplication concerns:

However, this could be an interesting approach.

Best regards