Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Sum of Distinct Records - Summary/Detail Tables

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

1 Solution

Accepted Solutions
Not applicable

Re: Sum of Distinct Records - Summary/Detail Tables

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!

1 Reply
Not applicable

Re: Sum of Distinct Records - Summary/Detail Tables

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!

Community Browser