Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
ben0109
Contributor III
Contributor III

SUM DISTINCT but keeping all entries

Hi,

I need to sum the values of all fields that has the same batch number - preferably in my load script. I have tried GROUP BY, but I still need all entries, so this does not work. I simply want to sum the amount, if the batch number is the same.

Below is an example of my table:

TypeAmountBatch
Sale100B123
Sale522B153
Payment156B631
Sale1684B123
Payment648B631
Payment6218B631

 

My end goal will be to have a table like the example below:

TypeAmountBatchNew Field
Sale100B1231784
Sale522B153522
Payment156B6317022
Sale1684B1231784
Payment648B6317022
Payment6218B6317022

 

Any help will be of great value.

Cheers,

Ben

2 Solutions

Accepted Solutions
tresesco
MVP
MVP

Try like:

t1:
Load * Inline [
Type Amount Batch
Sale 100 B123
Sale 522 B153
Payment 156 B631
Sale 1684 B123
Payment 648 B631
Payment 6218 B631] (delimiter is spaces);

Join
Load
    Batch,
    Sum(Amount) as BatchSales
Resident t1 Group By Batch;

View solution in original post

ben0109
Contributor III
Contributor III
Author

Hi 

Thanks for your reply. It seems to work.

I have also stumbled upon another solution that can also work:

SUM(Amount) OVER(PARTITION BY Batch) AS [Batch Sales].

Cheers

View solution in original post

2 Replies
tresesco
MVP
MVP

Try like:

t1:
Load * Inline [
Type Amount Batch
Sale 100 B123
Sale 522 B153
Payment 156 B631
Sale 1684 B123
Payment 648 B631
Payment 6218 B631] (delimiter is spaces);

Join
Load
    Batch,
    Sum(Amount) as BatchSales
Resident t1 Group By Batch;

ben0109
Contributor III
Contributor III
Author

Hi 

Thanks for your reply. It seems to work.

I have also stumbled upon another solution that can also work:

SUM(Amount) OVER(PARTITION BY Batch) AS [Batch Sales].

Cheers