Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
phase2medical
New Contributor III

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

Re: Re: Joining multiple tables, and then converting rows to columns

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

11 Replies
Not applicable

Re: Joining multiple tables, and then converting rows to columns

May you use pivot instead of straight table?

phase2medical
New Contributor III

Re: Joining multiple tables, and then converting rows to columns

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

Re: Re: Joining multiple tables, and then converting rows to columns

look here:

let ma know if it helps.

regards

Darek

phase2medical
New Contributor III

Re: Re: Re: Joining multiple tables, and then converting rows to columns

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.

phase2medical
New Contributor III

Re: Re: Re: Joining multiple tables, and then converting rows to columns

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

Re: Re: Re: Re: Joining multiple tables, and then converting rows to columns

so, try v1

Not applicable

Re: Re: Re: Joining multiple tables, and then converting rows to columns

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

I hope, that it helps.

regards

Darek

Re: Re: Re: Re: Joining multiple tables, and then converting rows to columns

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

phase2medical
New Contributor III

Re: Joining multiple tables, and then converting rows to columns

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

Community Browser