Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rogeriogyn
Partner - Contributor III
Partner - Contributor III

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:

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

];

Labels (2)
1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

2 Replies
swuehl
MVP
MVP

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
rogeriogyn
Partner - Contributor III
Partner - Contributor III
Author

Thats it

Thanks