Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
johnnyjohn
Creator
Creator

Sum when two fields have the same value

I need some help for solving something below. Please note the example is a highly abstracted version of my problem, but highlights well what I need to accomplish.

So I have 2 tables (n1 and n5 - shown below).

johnnyjohn_5-1683038197434.png

Because of some broader complexities with our data model, I need to create DYNAMICALLY on the front end, not in the load script, a table that shows as below (I cannot merge the n1_name and n5_name into a single table via the load script).

johnnyjohn_6-1683038255691.png

I've managed to get something kind of working by doing the below

n1_name    |    sum(n1_dollar)    |    SUM(IF(n1_name = n5_name, n5_dollars))

Except the issue with the third column is that because John appears twice on the n5 table, it will double count the n5_dollars = 2, so the output will be 4, which is incorrect. 

To re-iterate, I cannot change anything with my data model, I'm trying to find a script only solution. 

Many thanks 

 

 

 

Labels (3)
1 Solution

Accepted Solutions
Kushal_Chawda

@johnnyjohn  try below

=sum(aggr(if(n1_name=n5_name,sum(n5_dollar)),n1_name,n5_name))

View solution in original post

3 Replies
MarcoWedel

how are those tables associated?

johnnyjohn
Creator
Creator
Author

For the sake of this example you can assume they are not connected. In reality they are not island tables but  re-evaluating the entire data model would be overdoing it. Thanks! 

Kushal_Chawda

@johnnyjohn  try below

=sum(aggr(if(n1_name=n5_name,sum(n5_dollar)),n1_name,n5_name))