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?