we have a problem in our three-tier QlikView architecture.
Unfortunately, the context is somewhat too voluminous to post it here, so this must remain rather theoretic. It's a question of logic I think.
I will try to formulate the problem as clearly as possible and, if needed, I will provide additional information.
First, a little bit of fundamental background:
- We have a nr. of items, each of which is uniquely identified by a number - which is now enhanced with the plant_key,
as we have several plants => looks like this: >> 74|123456 << - that would be item # 123456 in plant 74
- A varying number of these items are stored in packages, each of which has a unique package_ID.
- The packages have a certain status - "started" and "closed", to name only two. "Closed" is the last, logically.
- Each item_nr. has certain time_measures associated, the time it takes to pack (in a box) and to process it.
=> So far for the most fundamental background.
The problem is this
Those times are actually in the system per item_nr (with some more keyfields) - that is also how they are joined (using an IntervalMatch) to a table PACKAGE_ITEMS. (more details in the attached txt)
=> That table PACKAGE_ITEMS is linked to a table labeled ITEM_MASTER via the package_ID (which is a perfect key in the PACKAGE_MASTER and very close to it in the PACKAGE_ITEMS)
=> From what I understand about it, the times (pack_time and process_time) should then be joined to every record that exists in PACKAGE_ITEMS with this item_nr - which might be n records (each with a unique package_ID, but the same item_nr.)
=> The times should, then, be available regardless of the STATUS (which is associated with a specific package - a package can logically have only one status, but as I said n packages can exist with the same item_nr.)
<=> That is not so: The times are visible only for packages with one status - and I don't know why because the status does not figure in the key used to join - at least I think so. It's a bit difficult since QlikView doesn't let you specify exactly which field to use as a join_field.
I will post the entire block of code that might be relevant in the attachment, with a few comments.
After looking at the code in the attached, txt, these would be the things I'd like to know:
- Is this construction correct such as it is (the IntervalMatch and the ensuing JOIN, that is)?
- What might be the reason why the time_measures, although actually stored per item_nr. (et al.), are not
available for every possible status although the status (specific to one package_id) does not figure in the key?
Intervalmatch creates a link table with fields Changekey, Valid_from, Valid_until, PACKAGE_ITEMS.END_DATE.
So for each Changekey it links the interval (Valid_from, Valid_until) with corresponding PACKAGE_ITEMS.END_DATE.
Usually you don't want such a complex relation, so you join the facts there, so you see in the script, and drop the unnecessary fields keeping them in another table.
It's hard to answer the question provided since there is no statuses in the script.
Knowing that the code is correct such as it is is already a big help.
Could you just explain to me why we have one INTERVALMATCH() and then another JOIN from the same table without that fct.? How exactly does that work? I'd like to leave a comment in the code because I don't understand it.
Hope this resolve your issue. If the issue is solved please mark the answer with Accept as Solution & like it. If you want to go quickly, go alone. If you want to go far, go together.
that document is a simple and easy-to-understand explanation of the simple form of IntervalMatch - but it doesn't help me understand the code I have.
It's not the enhanced form of the IntervalMatch - that is not hard. In addition to the fields MIN (Valid_from in my case) and MAX (Valid_until in my case), an additional keyfield (Changekey in my case) is compared since there might be several records that have the date_field (END_DATE in my case) between MIN and MAX.
<=> If there is only one LOAD with the IntervalMatch() prefix, that's easy enough
<=> What does my code do and why is it correct, that's what I'd like to know? We built the code with the help of an external consultant, so it doesn't surprise me that it is correct - but why? What is the function of this sequence of
- one LOAD with IntervalMatch (but no data_fields in the LOAD except the MIN, MAX and the additional key) and
- one more LOAD with a lot of data_fields, but no IntervalMatch?
Thanks a lot!
Best regards,
DataNibbler
P.S.: Well, in further researching this error, it seems that maybe the JOIN is working as expected, but there is just no matching key available for some packages - which is even stranger because, as I said, there may be n packages with the same item_nr., so when one of those has the key, they all should.
Still, if anyone could explain just what that code I posted does and if and why it's correct in terms of the IntervalMatch, I think that would be a great help in thinking this through.
P.P.S.: I just tried, according to what I would have thought, taking some lines out of that second JOIN (the one with no IntervalMatch() prefix) and pasting it into the first and then deactivating the second JOIN - but then the very next command in the script fails because a field is not found - so it seems the second JOIN is necessary. But why?
You have some attributes that is connected with intervals, not with 'events'.
So you can make the second join load to add these data to the link table just to omit the unnecessary links between tables. It makes sense when you cares about performance and don't care that the attributes will be copied for each event that connected with that interval. So it's suitable for dimension/flag/classification attributes.
thanks for the explanation - but, sorry, I still don't understand.
By modifying the script a little and reloading, I found out that the second JOIN is then done on
- the changekey as well as
- valid_from and
- valid_until
=> Those are the fields encompassed in the first JOIN (with the IntervalMatch prefix)
<=> The changekey, for one, is already present in the table before that JOIN from ITEM_PACKTIMES.
=> Is there some invisible pointer that gets added by that IntervalMatch_JOIN and thus makes the changekey which in itself isn't unique as the times might have been changed) really unique?
=> That is the only way I can picture this. Apart from the info "which interval formed by valid_from and valid_until the END_DATE in PACKAGE_ITEMS falls into, everything is already present in the table.
Best regards,
DataNibbler
P.S.: Ah - I see. Some more re-running the script with different EXIT points shows that the fields valid_from and valid_until are added to the table PACKAGE_ITEMS by that IntervalMatch_JOIN. With the addition of those two fields, the changekey is unique and it is subsequently used in the following JOIN.
As I said, the "feature" of QlikView to not let you specify exactly which field(s) to join on - the JOIN just automatically uses suitable fields, but one has to know which those are - makes it somewhat difficult.
Is that correct?
If so, that point is cleared and I will add a comment so the next one reading that code will know immediately what happens.
<=> That still leaves me puzzled as to why no times are being joined to so many records. I'll check whether there might be something wrong with our historization_app.
