Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Thanks Marcus for your input.
Managed the produce the amounts in a single load statement without using the resident and it works as expected.