Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
HI
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..
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.
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
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 ?
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.
Hi
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 ?
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.
HI
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..
Hi Mayil!
Thank's a lot for your help!
The pivot table looks fine now and I have learned more about QlikView.