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: 
Anonymous
Not applicable

Joining multiple tables, and then converting rows to columns

Hello --

I have a project to create a production variance dashboard. I'm in the initial stages of trying to gather all of my data.

One of the key aspects to this dashboard is it needs to display a straight table that shows the work orders completed on a day, the qty completed, and break out the material, labor, and overhead costs to then calculate some variances.

They wish to see this table like so:

Part NoWork OrderQty CompletedStd. Material CostStd. Labor CostStd. Overhead Cost
123456XYZ10.05.28.32

The trouble I am having is that my data exists in 3 tables as follows:

Transaction Table:

Part NumberWork OrderQty Completed
123456XYZ10

Cost Table:

Part NumberCost_IDStd. Cost
1234561.05
1234562.28
1234563.32

Cost_ID Table:

Cost_IDCost_Description
1Std. Material Cost
2Std. Labor Cost
3Std. Overhead Cost

How do I join these various tables together, while converting the "rows" of the cost descriptions into columns in order to display the values the way they wish? Further more, I'll then need to utilize the various costs to do further calculations.

I appreciate your help!

1 Solution

Accepted Solutions
Not applicable
Author

i see.

in fact, even having data model i proposed, u may still use straight table. Simple create expressions for each KPI using set analysis.

Of course you may also create your model like Marco suggested. Of course the question is, what else (except this chart) you plan to do in your application

regards

Darek

View solution in original post

11 Replies
Not applicable
Author

May you use pivot instead of straight table?

Anonymous
Not applicable
Author

Hi Dariusz --

Thank you for your reply. I could possibly use a pivot table, however they had requested straight table in their design.

There will be additional calculated columns within the table to calculate the variances from std. to actual reported and they do not want users to be able to manipulate the layout for this.

Thanks!

Not applicable
Author

look here:

let ma know if it helps.

regards

Darek

Anonymous
Not applicable
Author

Hi --

I'm sorry, I forgot to make a note of something. The transaction log table will have multiple transaction records for a part & a day that will need to be added together to get the total transaction qty:

Example:

Transaction DatePart NumberWork OrderQty
4/24/2014123456XYZ5
4/24/2014123456XYZ5

This would need to be summarized as 10.

Anonymous
Not applicable
Author

I get somewhat strange results when I try to pivot it in this fashion - resulting in a lot of collapsed rows.

qlik.png

Not applicable
Author

so, try v1

Not applicable
Author

try to check "Always fully expanded" on Properties/Presentation Tab

I hope, that it helps.

regards

Darek

MarcoWedel

Hi Darek,

I agree, that the cost type should remain a single field/dimension, but if it is required to generate seperate columns in a straight instead of a pivot table, how about this approach:

QlikCommunity_Thread_115553_Pic1.JPG.jpg

QlikCommunity_Thread_115553_Pic2.JPG.jpg

CostMap:

Mapping LOAD *

FROM [http://community.qlik.com/thread/115553] (html, codepage is 1252, embedded labels, table is @4);

tabCost:

Generic LOAD

  [Part Number],

  ApplyMap('CostMap', Cost_ID),

  [Std. Cost]

FROM [http://community.qlik.com/thread/115553] (html, codepage is 1252, embedded labels, table is @3);

tabTransaction:

LOAD [Part Number],

     [Work Order],

     Sum(Qty) as Qty    

FROM [http://community.qlik.com/thread/115553] (html, codepage is 1252, embedded labels, table is @5)

Group By [Part Number], [Work Order];

hope this might help

regards

Marco

Anonymous
Not applicable
Author

Hi Dariusz -

You're answers have been very helpful and I'm getting close, but now I am running into the issue which is why I believe they requested a straight table.

When I try to do additional formulas against my costs, I get a column for each formula that shouldn't be there.

For example, I need to subtract a value from only the Std. Labor Cost - however when I do this, I get that calculation for each of the different cost types resulting in additional columns that I don't want. See example image below:

qlik2.png