1 Reply Latest reply: Sep 24, 2016 4:52 PM by Sunny Talwar RSS

    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

        • Re: How do I add missing or 'dummy data'?
          Sunny Talwar

          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;

           

          Capture.PNG