2 Replies Latest reply: Dec 22, 2015 1:30 PM by Rogério Faria

# Conditional Associative Tables

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:

 PlantID DepartmentID Item 1 - Administrative 2 201 HR Expenses 2 202 Oil Cost 3 - Operational 3 304 Cables 3 405 Pipes

ValuesTable:

 Date PlantID DepartmentID Value 2015-01-01 1 - US\$ 15.00 2015-01-01 1 101 US\$ 25.00 2015-01-01 2 201 US\$ 25.00 2015-01-01 2 202 US\$ 35.00 2015-01-01 3 - US\$ 25.00 2015-01-01 3 304 US\$ 25.00 2015-01-01 3 405 US\$ 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:
PlantID,DepartmentID,Item
2,201,HR Expenses
2,202,Oil Cost
3,NULL,Operational
3,304,Cables
3,405,Pipes
];

ValuesTable:
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

Maybe like

```StructureTable:
Load *, Autonumber(PlantID & DepartmentID) as Key Inline [
PlantID,DepartmentID,Item
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