Data Relations

[#1]

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

[#2]

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

[#3]

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

[#4]

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.

[#5]

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?

[#6]

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.

[#7]

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:\

[#8]

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.

[#9]

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.

[#10]

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

[#11]

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!