topic Sum across multiple tables in QlikView

Sum across multiple tables

Anonymous — Thu, 18 Apr 2013 09:59:54 GMT

I have two tables in my data model. One that contains dates and transactional data keys, and one that contains the transactional data. The reason for this is a single transaction can appear against multiple dates, and I don't want to store the transactional data (approx. 100 fields) multiple times.

The problem I'm having is when I sum an amount field from this transactional data over a trend it's working fine. For example a single transaction with an amount of £500, that is recorded against three separate dates is correctly displaying that £500 three times.

If I take away the date from the chart, the aggregation only displays a total of £500. Now, this would generally be correct, however, the customer wants to see a total of £1500 in this case.

Does anyone know a way to achieve this using advanced aggregation? I've wracked my brains, and I've come up with nothing.

Thanks

Re: Sum across multiple tables

kaushiknsolanki — Thu, 18 Apr 2013 13:38:11 GMT

Hi,

The only way i feel is you get the Date field in your transactional table.

Something like this.

Load Date, TransactionalKey from A;

left join

Load TransactionalKey,.....From B;

Regards,

Kaushik Solanki

Re: Sum across multiple tables

Anonymous — Thu, 18 Apr 2013 13:45:40 GMT

Sadly that would result in the transactional data being duplicated in the table, so it's not an option.

A single transaction with 100+ fields cannot be repeated, potentially, 14 times (once for each reporting month) because of the memory implications.

Re: Sum across multiple tables

kaushiknsolanki — Thu, 18 Apr 2013 16:25:36 GMT

Dear Andrew,

Even though it will duplicate the records, qlikview stores only distinct values in memory, so it shouldnt increase the memory.

Still will do some testing and get back to you.

Regards,

Kaushik Solanki