Summation of different fields from temp tables

[latheesh]

Hi, I have a requirement and need help on this.

I have 6 temporary table by using resident from other database table and each of them has a field(amount) with common field.

Example..

Table Temp1 has productid, amount1

Temp2 has productid,amount2 and so on (6 temp tables)

Finally I need to have the sum of all these amounts from 6 different temp tables (amount1+amount2+...+amount6)

I need to use this summerised amount in the set analysis expression while creating some charts and tables.

Please anyone help on this.

Thanks in advance.

[marcus_sommer]

Within the  most scenarios such an approach has massive disadvantages - in regard to handle and address all these multiple measure-fields within the script and the UI and often it creates a lot of difficulties and efforts to get rid of all the synthetic keys and/or circular references - often solved with link-tables but they aren't always trivial, doesn't solve all challenges and could become a performance-problem.

Better is therefore mostly to concatenate all these facts into a single table by harmonizing the fields. In your case it may look like:

final: load Id, Amount1 as Amount, 't1' as Source from t1;
concatenate(final) load Id, Amount2 as Amount, 't2' as Source from t2;
concatenate ....

Means there is only a single field Amount which could be aggregated and with the field Source you are able to differ between the sources per selection and/or dimension and/or set analysis.

- Marcus

[latheesh]

Thanks Marcus for your input.

Managed the produce the amounts in a single load statement without using the resident and it works as expected.