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.
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.
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.
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.
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?
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.
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:
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.
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?
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
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.
