topic Data Relations in QlikView

Data Relations

Fri, 19 Jun 2009 20:46:04 GMT

Hi guys,

I'm both new to Qlikview and relatively new to relational databases. Everything has gone really well with Qlikview but i'm having a problem. It seems to be simple but I haven't been able to figure it out.

I have a set of Orders in my data that have a bunch of relations in a database. Most of the information that I need has already been retrieved and stored in a QVD file. All of this data is linked to the "Order ID" field of the order. We've realized that we need a bit more data from the database, so I'm augmenting the data in the QVD with a couple of extra pieces from the DB. I've been able to retrieve everything properly, the problem is that all of this data isn't linked to the "Order ID" field, but to an "orderKey" field, a background hex key that all of the data is actually related with.

So my problem is that I have a bunch of data related to "Order ID", and the new data I've gotten is related through "orderKey" which is in turn related to "Order ID". The problem with this is that I have this split in data of things that are related directly to "Order ID" and things that are related indirectly to it using "orderKey".

My question is: is there a way to make all of this new information act as if it is being related directly to "Order ID"?

As it stands now in tables there are splits along the data for each order, which makes the data really messy and unorganized.

Any help would be greatly appreciated!

Thanks,

Dan

Data Relations

Fri, 19 Jun 2009 21:17:22 GMT

Since it sounds like there is a direct correlation between OrderID and orderKey, you should be able to link everything together. You need to load a table or something to link OrderID to orderKey. The nice thing about QlikView is that once you load the fields with the correct names, it will link them for you.

Data Relations

Fri, 19 Jun 2009 21:26:50 GMT

they are already linked in the initial QVD. The weird thing is that the information is going directly to Order ID instead of through orderKey. So when I display all of the old and new information in a table, I get something like this:

Order ID | orderKey | old Info | newInfo
---------------|---------------------|------------------|---------------
123 | N/A | sample | N/A
| 1AF3 | N/A | sample2

I'm getting all of these N/A fields due to this split in data, as some fields are relating straight to order Id, and some are through orderKey, instead of having all of the data displayed together. Perhaps I am misunderstanding your solution.

Data Relations

Fri, 19 Jun 2009 21:51:20 GMT

I think I need some more information on what your data looks like and how you're loading it.

Use Ctrl + T to look at the Table Viewer. That will give a visual representation of how your data is linked. What fields are linked between the old dataset and the new?

You may need to do a Concatenate load. That is kind of like a union. Something like this:

LOAD *;
SQL SELECT
 OrderID,
 NULL As orderKey,
 Info
FROM OrderID_Data;
CONCATENATE LOAD *;
SQL SELECT
 NULL As OrderID,
 orderKey,
 Info
FROM OrderKey_Data;

That should allow you to match up the fields in each data set. You may also need to do something to get the keys to work together. Perhaps creating a new key that is a combination of both keys. It will kind of depend on your data.

Data Relations

Fri, 19 Jun 2009 22:11:00 GMT

I've been trying to work with concatenate load but I haven't been able to use it properly.

The original data from the initial QVD is all stored in one giant table called Merge. Everything for each order is in there and I guess it was all organized when the initial QVD was created.

The relations I've added are much more stringy. They all link from the orderKey out of the Merge table, and there are several branches beyond that linking to more keys and eventually linking to the data that I want.

When I've tried the concatenate load statement the data i'm concatenating appears to be empty. I have something like:

CONCATENATE LOAD orderKey,
dsRequestKey;
SQL SELECT orderKey,
dsRequestKey
FROM MDsSession.dbo.DsRequest;

which attaches the field dsRequestKey to the Merge table, however when I try to view this data after it's been concatenated it all appears to be null, even though it works fine if I do a regular load statement and load it into it's own table.

Perhaps I am missing a step, I see that you are including these NULL values for OrderID in the second load statement. In this example, the mapping table i'm using to link the two keys doesn't have OrderID in it. Would including this NULL OrderID part of the statement change what I am doign in this case?

Data Relations

Fri, 19 Jun 2009 22:56:08 GMT

How are you loading the first QVD, the one before the concatenate? I probably don't need to see all the detail fields, but if you could show how your are loading your QVD with the OrderID data, it may help.

When doing the Concatenate, you want each dataset to have the same number of fields and the same field names. If you were doing a union query, the database would probably force you to do this. The reason I used the NULLs is because each dataset does not have all the same fields.

What you should do is try to do the simplest load possible. Pick one field that is in both datasets but is not a key. For the OrderID data, load OrderID, NULL for orderKey and then load your additional field. For the orderKey data, load NULL for OrderID, orderKey and your additional field. Make sure the additional field is named the same in both cases. Then when you load this, you will have a dataset with three fields. Either the OrderID or the orderKey field will be populated (but never both) and the additional field should be populated for both sets.

Also, what is the dsRequestKey in your sample?

Data Relations

Sat, 20 Jun 2009 00:01:59 GMT

ah I guess thats where the problem is. The fields I'm trying to bring in are not from like tables, so concatenation wouldn't work. In the example dsRequestKey is just one of the fields I'm trying to link to Order ID through orderKey.

This is how the QVD called Merge is being loaded, along with another called CalendarDates.qvd:

//load data
load * from ..\qvd\Normalized\Merge.qvd (qvd);
TEMP:
LOAD minOrderedDate,
maxOrderedDate,
minReportDate,
maxReportDate
FROM ..\CalendarDates.qvd (qvd);
LET vMinDate = peek('minOrderedDate', 0, 'TEMP');
LET vMaxDate = peek('maxOrderedDate', 0, 'TEMP');
LET vReportMinDate = peek('minReportDate', 0, 'TEMP');
LET vReportMaxDate = peek('maxReportDate', 0, 'TEMP');
DROP TABLE TEMP;

I was actually able to successfully bring the new data (dsRequestKey) into the Merge table using a join statement:

join load distinct orderKey,
dsRequestKey;
SQL SELECT orderKey,
dsRequestKey
FROM MDsSession.dbo.DsRequest;

However the dsRequestKey is still exclusively linked to orderKey, not Order ID. Is there some sort of variant join statement that I can use to create this link to Order ID?

By the way I just want to thank you very much for your help on this so far.

Data Relations

Sat, 20 Jun 2009 00:42:10 GMT

Do you have a method of linking OrderID to orderKey?

You could use an intermediate table: SELECT orderKey, OrderID FROM OrderCorrelator. Then when you load that in, OrderID will be linked to orderKey and vice versas. What is the relationship between OrderID and orderKey?

Data Relations

Sat, 20 Jun 2009 01:03:15 GMT

They are already related in the Merge table. The problem is that the rest of the data in the Merge table is related directly to Order ID, but the new information I want can only be attained using the orderKey. So there is a split in the data at the orderKey level, but I want it all to be together, related to order ID.

I want to somehow make the data "think" it is directly related to Order ID, even though it is actually being retrieved using orderKey

Data Relations

Sat, 20 Jun 2009 01:39:57 GMT

So, in the merge table, you have an OrderID and an orderKey for every record? If so, then just link the new data to the orderKey. Then you'll be able to go from new record to orderKey to OrderID.

Basically, if OrderID and orderKey are already correlated, then all you have to do is link the new data to the orderKey and via that, you can get to OrderID.

Data Relations

Sat, 20 Jun 2009 01:44:14 GMT

Yeah it's already linked, but for the already existing information in the Merge table, its linked directly to the Order ID. So when I bring in this information which is linked to orderKey, when I try to display all of the data for an Order, there is a split between old and new in the table, which results in displays of "N/A" whenever the data isn't present in a certain branch of relations

Data Relations

Sat, 20 Jun 2009 01:49:44 GMT

Here is an example. For order 1218, all of the information to the right of the N/A dsRequestId (the orderKey) is from the Merge table. All of the information next to the non-N/A dsRequestId is the new information that I've added. There are a large number of N/A entries in this table as a result of the split, and it makes it difficult to read. I want to connect these two sets of data so they are displayed in a nicer way

Data Relations

Sat, 20 Jun 2009 02:43:04 GMT

Because you're getting N/A that suggests that something isn't linked correctly. I'm still not sure how your data is organized, which makes coming up with a solution difficult.

If OrderID and orderKey are linked in your old data, then you essentially have a table, Merge, that contains an OrderID and an orderKey table. If that is the case, then for every OrderID, there is a corresponding orderKey. Is this correct?

Now, you're trying to link a new set of data to the orderKey. This should be fine. Now, you should be able to take any record from this new data and find its orderKey. Then you should be able to take that orderKey and look up a corresponding OrderID in your old data.

I can think of two scenarios where the issues you describe would come up.

1. The orderKey for some of your records in the new data is not found in the old data. In these cases, the new data is not linked to anything in the old dataset and you would have no values for the old data fields. To figure out if this is the problem, grab a record from the new data and manually look up the orderKey in the old data. You should find the orderKey in the old data and from there you should be able to find a corresponding OrderID in the old data.

2. Your linking is off somewhere. From what you describe, you want to link from the orderKey in the new data to the orderKey in the old data, which is already linked to the OrderID in the old data. If everything is loaded correctly with the proper names, QlikView should do all of the linking for you. This is where the Table Viewer can help. You should see the new data with a link from old data to new data. Then either your old data already contains OrderID and orderKey or there is some link that connects the orderKey in the old data with the OrderID in the new data.

From what you have described, you have everything you need the make this work. Obviously, something is not set up correctly as you are getting missing data.

I've attached a QVW that has this simplified. I have two scenarios set up. One is very simple, where OrderID and orderKey are together in the old data. Two has an intermediate table which links the old and the new by having a table that connects OrderID to orderKey. In both cases, data flows across. I did leave a few missing values in there to show what happens when there is no value to link to.

Data Relations

Sat, 20 Jun 2009 02:55:50 GMT

Sorry, I posted before I saw your latest post. Could you take a screenshot of your Table Viewer (Ctrl + T)?

OrderID doesn't seem to be the problem as both datasets look to be linking back to it. The one thing that did throw me off was the gender field. That is the only field that is populated for both datasets.

This appears to be what you would get if you unioned up both datasets. You got rid of the concatenated load, correct?

Data Relations

Sat, 20 Jun 2009 03:06:44 GMT

Here is the table viewer

The gender field is odd, as I never created that relation. Both relations (from OrderID and dsRequestId/orderKey) must have already been in the Merge table.

I got rid of the concatenate load, as well as the join statement I mentioned earlier, so it is in it's original state.

Data Relations

Sat, 20 Jun 2009 03:21:20 GMT

Okay, there is nothing that is obvious on the table. One question I did have is that dsRequestId appears in Merge, but looking at the picture of your chart, dsRequestId is N/A on the merge lines. Is dsRequestId populated in the Merge dataset? If so, I would expect to see values for that field.

One idea would be to simplify your data a bit. Try making a duplicate of your application and getting rid of these tables:\

Data Relations

Sat, 20 Jun 2009 03:34:11 GMT

The dsRequestId that I get is from the Merge table, which is why that N/A is so odd. I had thought that the N/A in the dsRequestId column in the Merge line was a result of that data being related directly to Order ID, but really, it shuold still be populated anyway now that I think about it.

I require all of the data that is there so I can't simplify it, unless you mean for the purposes of figuring out this issue.

Data Relations

Sat, 20 Jun 2009 03:58:38 GMT

Yes, I meant trying to delete those tables as a test. I would do it in a duplicate of your app, so you don't mess with anything important. I seem to recall examples where having more than two tables linked by the same field can cause problems. I don't know why it would, but it's worth a try.

You may even want to just look at your Merge data and make sure dsRequestId is populated with values that can be found in the DsRequest table. Thinking about it further, I don't think this is the case. If dsRequestId did not have linkable values, then you wouldn't be able to get to OrderId from the new stuff and all of your new stuff would appear together in that chart with a blank for the OrderId.

Try to test what happens when you have the minimum tables needed to make the connection (Merge > DsRequest > AdviceLog > DsRuleScreen) from new to old.

Data Relations

Mon, 22 Jun 2009 19:59:51 GMT

I tried eliminating tables so that all Iw as left with are the Merge, DsRequest, AdviceLog, and DsRuleScreen tables. The data still contains the split in the table

Data Relations

Mon, 22 Jun 2009 20:54:13 GMT

I've decided to display this information in a way that doesn't hinge on this relation for neatness. It would have been nice to figure out but I don't want you or I to spend any more time on it if it isn't absolutely necessary. I thank you very much for the help and time you spent!