topic Re: Pivot table or straight table? in QlikView

Pivot table or straight table?

Anonymous — Sun, 04 Nov 2012 12:18:48 GMT

Hi!

I have a problem with showing some information in a dashboard. I want to show information from 2 tables in the same table in a dashboard.

We have a big data modell with many tabels loaded from MS-SQL into qvd-files. My need is as follows:

One table named "OCMQM1" contains information about incoming orders. Example of fields:

OCMQM1.Number = unique reference for the order (Q1001)

OCMQM1.Company = name of the company

OCMQM1.Open_time = when the order were registered

etc.

The orders are often so complicated that they have to be divided into sub-orders. We have a table called "OCMLM1" that contains information about sub-orders. One order can have many sub-orders. Example of fields:

OCMLM1.Number = unique reference for this suborder (Q1001-01)

OCMLM1.Company = name of the company

OCMLM1.Open_time = when the suborder were registeret

OCMLM1.Parent_order=Reference to the main order in "OCMQM1

etc.

The table for sub-orders has a field with reference to the "OCMQM1"-table, so there is no problem to link this two table together (OCMLM1.parent_order are linked to OCMQM1.number).

My need is to show information about both the main-order and the belonging sub-orders in the same table in QlikView (if possible). I shall not do any calculations, but only show fields from both the tables. I need to use expressions tho select which orders to show. I know it is a possibility to have 2 tables, but the best solution is to show all information in one table.

I want to show something like this in the dasbboard (orange fields are from table OCMQM1 and green fields from table OCMLM1):

Main-order Open-time Company Assigned-to Status Sub-order Assigned-to Status

Q1001 12.11.01 XXXXX IT-RT-MAIL Open Q1001-01 IT-RT-Network Pending

Q1001-02 IT-RT-SAN Open

Q1002 12.11.02 XXXXX IT-RT-SAN Open Q1002-01 IT-RT-Mail Closed

I have 2 dimensions (order and sub-order), that shall show information from 2 different tables. I suppose the solution is very simple, but I have been testing with pivot-table without finding a solution. I have such a pivot-table working fine in Excel, but I have problems with fixing it in QV.

I am grateful for any help with this problem. Must I use something like "Dimensionality"?

Best regards

Torunn

Re: Pivot table or straight table?

whiteline — Sun, 04 Nov 2012 16:12:31 GMT

Hi.

You should load your data in a right way. I've mentioned that there is no problem to link the tables.

Have you linked them in QV ?

(QV links tables by similar field names)

You can have dozens of tables in QV data model and use different fields from different tables in one chart (pivot).

You should also have to make some expression to see the rows (pivot or straight table).

Try =count(OCMLM1.Number) for example.

Re: Pivot table or straight table?

Anonymous — Sun, 04 Nov 2012 18:34:58 GMT

Hi again!

Yes, we have linked them in QV.

I can see the rows from both tables when I am testing, but I am not able to create a table that shows both main-order information and information about all the associated sub-orders in the same table. The first columns in the table shall show info about the main-order and the rest of the columns info about each sub-order. We can have up to 15 sub-orders for some orders.

Shall the unique id-field to the main order and the unique id-field to sub-orders be dimensions? The pivot table works fine if I only select these two fields as dimensions and select other fields from the sub-orders as expressions. How can I show several fields from the main-order, before the info about sub-orders are shown? Must these fields also be dimensions?

I have a feeling that the solution is very simple, but I am not able to see it.

Best regards

Torunn

Re: Pivot table or straight table?

whiteline — Mon, 05 Nov 2012 07:18:10 GMT

If you just add as dimensions (pivot table mode):

OCMQM1.Number

OCMQM1.Company

OCMQM1.Open_time

OCMLM1.Number

OCMLM1.Company

OCMLM1.Open_time

and as expression:

=count(OCMLM1.Number)

Isn't it almost what you want ?

Re: Pivot table or straight table?

Anonymous — Mon, 05 Nov 2012 20:10:06 GMT

Hi again, Whiteline!

Yes, this is almost what I want!

The only thing missing now is that I don't need to show the expression-column. Do you have a solution of how to hide the expression column in the pivot table?

I tried to hide it using a variabel in Chart Properties/Expressions Conditional, but then disappeared all the dimensions also.

Re: Pivot table or straight table?

MayilVahanan — Mon, 05 Nov 2012 20:28:57 GMT

Are you only need the dimension alone in your chart?

OCMQM1.Number

OCMQM1.Company

OCMQM1.Open_time

OCMLM1.Number

OCMLM1.Company

OCMLM1.Open_time

Without any expression? Then you can go with table box..not understand why you try to hide the expression-column..can you explain it ?

Re: Pivot table or straight table?

Anonymous — Mon, 05 Nov 2012 20:43:12 GMT

Hi!

I don't think a table box will solve my problem. I shall not show all the records in the order-table, but only orders in a certain status. When I use this expression in the pivot-table I get the correct number of records in the table:

=SUM({$<SM9.CASES.TYPE={'QUOTE'}>}SM9.CASES.OPEN_COUNT)

Maybe I could use calculated dimensions in the pivot instead?

As you understand of my questions, I am quite new to QlikView.

Re: Pivot table or straight table?

MayilVahanan — Mon, 05 Nov 2012 21:38:58 GMT

You want to display only fields but not for certain condition,don't want to use any function like count/sum ..

If so, use

Expression:

only(FieldName) // by using this , pls see about only function in qv help

Calculated dimension:

try like this:

if(match(SM9.CASES.TYPE,'QUOTE'),SM9.CASES.TYPE)

sorry, if i understand anything wrong means..

Re: Pivot table or straight table?

Anonymous — Mon, 05 Nov 2012 22:04:31 GMT

Hi Mayil!

Thank's a lot for your help!

The pivot table looks fine now and I have learned more about QlikView.