How do I add missing or 'dummy data'?

[randy_kluver]

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

[sunny_talwar · Accepted solution]

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;