Sum of Distinct Records - Summary/Detail Tables

[#1]

Hi Community Users,

I have a huge data set of ~25 million records. It has a unique identifier for each record (RecordID)

When I join this dataset with another dataset, QlikView performs a one-to-many join and hence in the final dataset I have multiple RecordID's

What I need to do is get a sum of Amount_Field from the final data set. If a use a sum formula it sums up duplicate RecordID's. If I use

SUM(DISTINT Amount_Field), it sums up unique amount values.

So I found a solution within community to use SUM(Aggr(Sum(DISTINCT Amount_Field), RecordID)).... it works but has significant performance issues (~25 million records)

I intend to use Summary and Detail tables (QVDs) with drill-through enabled. The problem with this approach is that QlikView would concatenate the two tables if they have similar field names...and I need to have similar field names to enable drill through...

Can you please advise a best approach to handle this?

Regards
Tarun

[#2 · Accepted solution]

A quick fix to this is:

Create a new variable to flag the first instance of RecordID using (if(exists(RecordID),0,1)) as FirstOccurence...

and use the FirstOccurence for all calculation conditions.

Performance improves exponentially... waiting for other solutions...

Thanks!