2 Replies Latest reply: Dec 22, 2015 1:30 PM by Rogerio Faria RSS

    Conditional Associative Tables

    Rogerio Faria

      I not sure on how to solve this issue. It looks like a condition association.

      I need to create a Simple Table, based on 2 other tables:

       

      StrutureTable:

      PlantIDDepartmentIDItem
      1-Administrative
      2201HR Expenses
      2202Oil Cost
      3-Operational
      3304Cables
      3405Pipes

       

      ValuesTable:

      DatePlantIDDepartmentIDValue
      2015-01-011-US$ 15.00
      2015-01-011101US$ 25.00
      2015-01-012201US$ 25.00
      2015-01-012202US$ 35.00
      2015-01-013-US$ 25.00
      2015-01-013304US$ 25.00
      2015-01-013405US$ 50.00

       

      This is what I can't achieve. The ResultTable should be:

      ItemValueDescription (just explanation)
      AdministrativeUS$ 40.00 Sum(Value) where PlantID=1, no matter what DepartmentID
      HR ExpensesUS$ 25.00 Sum(Value) where PlantID=2, DepartmentID=201
      Oil CostUS$ 35.00 Sum(Value) where PlantID=2, DepartmentID=202
      OperationalUS$ 100.00 Sum(Value) where PlantID=3, no matter what DepartmentID
      CablesUS$ 25.00 Sum(Value) where PlantID=3, DepartmentID=304
      PipesUS$ 50.00 Sum(Value) where PlantID=3, DepartmentID=405

       

      I already tried to use STAR is *; and replace NULL with '*' on StructureTable and ValuesTable, it didnt worked.

      So, anyone can helpme on this one?

       

      Thanks

       

       

      If it helps, this is the code I'm using in the sample:

      Set NullInterpret = 'NULL';
      
      
      
      StructureTable:
      Load * Inline [
      PlantID,DepartmentID,Item
      1,NULL,Administrative
      2,201,HR Expenses
      2,202,Oil Cost
      3,NULL,Operational
      3,304,Cables
      3,405,Pipes
      ];
      
      
      
      
      
      ValuesTable:
      Load * Inline [
      Date,PlantID,DepartmentID,Value
      2015-01-01,1,NULL,15
      2015-01-01,1,101,25
      2015-01-01,2,201,25
      2015-01-01,2,202,35
      2015-01-01,3,NULL,25
      2015-01-01,3,304,25
      2015-01-01,3,405,50
      ];
      
      
      
      
      
        • Re: Conditional Associative Tables
          Stefan Wühl

          Maybe like

           

          StructureTable: 
          Load *, Autonumber(PlantID & DepartmentID) as Key Inline [ 
          PlantID,DepartmentID,Item 
          1,ALL,Administrative 
          2,201,HR Expenses 
          2,202,Oil Cost 
          3,ALL,Operational 
          3,304,Cables 
          3,405,Pipes 
          ]; 
          
          
          
          
          
          ValuesTable: 
          Load Date, PlantID as ValuesPlantID, DepartmentID as ValuesDepartmentID, Autonumber(PlantID & DepartmentID) as Key, Value Inline [ 
          Date,PlantID,DepartmentID,Value 
          2015-01-01,1,ALL,15 
          2015-01-01,1,101,25 
          2015-01-01,2,201,25 
          2015-01-01,2,202,35 
          2015-01-01,3,ALL,25 
          2015-01-01,3,304,25 
          2015-01-01,3,405,50 
          ];
          
          
          Concatenate (ValuesTable)
          LOAD Date, Autonumber(ValuesPlantID & 'ALL')  AS Key, Value
          Resident ValuesTable
          WHERE ISNUM(ValuesDepartmentID);
          

           

          Then create a straight table with dimension Item and as expression

          =Sum(Value)

           

          Enable 'Suppress When Values is NULL' on dimension tab.

           

          Item sum(Value)
          275
          Cables25
          HR Expenses25
          Oil Cost35
          Administrative40
          Pipes50
          Operational100