Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How do I load 'dummy data' from multiple resident tables?
Scenario:
I have several pivot charts where the users need to always see all the dimensions across the top. However when they filter down to a smaller dataset, some of the values (that don't exist) get dropped (as expected). [My idea (right or wrong)] on how to reverse that is to create some dummy data (with zeros) so that the dimensions are not dropped.
I have some additional constraints:
1. In my scenario the 'Show All Values' is greyed out - So can't use that
2. Allowing NULLS is not an option. (This created an additional column/row that I can't display)
3. I can't replacing the Dimensions with Expressions/Set Analysis
(i.e. Expression A = SUM(<{Division = {'A"}}> Amounts)
- The dashboard has conditional dimensions, so this solution wouldn't work for us.
My Question:
How do I create that data? (Or solve that issue)
See Attached example.
Thanks for looking,
Randy
May be by doing this:
Products:
LOAD * INLINE
[
Division, Product_ID
A, 1
A, 2
A, 3
B, 4
B, 5
C, 6
D, 7
];
Customers:
LOAD * INLINE
[
Cust_Name, Cust_Number
Cust1, 100
Cust2, 200
Cust3, 300
];
Sales:
LOAD * INLINE
[
Cust_Number, Product_ID, Amount
100, 1, 25
100, 2, 100
100, 3, 50
100, 4, 17
100, 5, 22
200, 4, 81
200, 5, 44
200, 6, 73
200, 7, 9
300, 1, 67
300, 4, 38
300, 7, 19
];
TempTable:
LOAD Cust_Number
Resident Customers;
Join (TempTable)
LOAD Product_ID
Resident Products;
Join (Sales)
LOAD *
Resident TempTable;
DROP Table TempTable;
May be by doing this:
Products:
LOAD * INLINE
[
Division, Product_ID
A, 1
A, 2
A, 3
B, 4
B, 5
C, 6
D, 7
];
Customers:
LOAD * INLINE
[
Cust_Name, Cust_Number
Cust1, 100
Cust2, 200
Cust3, 300
];
Sales:
LOAD * INLINE
[
Cust_Number, Product_ID, Amount
100, 1, 25
100, 2, 100
100, 3, 50
100, 4, 17
100, 5, 22
200, 4, 81
200, 5, 44
200, 6, 73
200, 7, 9
300, 1, 67
300, 4, 38
300, 7, 19
];
TempTable:
LOAD Cust_Number
Resident Customers;
Join (TempTable)
LOAD Product_ID
Resident Products;
Join (Sales)
LOAD *
Resident TempTable;
DROP Table TempTable;