Conditional Associative Tables

[rogeriogyn]

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:

Item	Value	Description (just explanation)
Administrative	US$ 40.00	Sum(Value) where PlantID=1, no matter what DepartmentID
HR Expenses	US$ 25.00	Sum(Value) where PlantID=2, DepartmentID=201
Oil Cost	US$ 35.00	Sum(Value) where PlantID=2, DepartmentID=202
Operational	US$ 100.00	Sum(Value) where PlantID=3, no matter what DepartmentID
Cables	US$ 25.00	Sum(Value) where PlantID=3, DepartmentID=304
Pipes	US$ 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
];

[swuehl · Accepted solution]

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
Cables	25
HR Expenses	25
Oil Cost	35
Administrative	40
Pipes	50
Operational	100

[rogeriogyn]

Thats it

Thanks