IntervalMatch advanced

[datanibbler]

I have used this function before, but not often.

It is not particularly helpful that the integrated HELP does not cite the same example for the simple and the advanced syntax ... but it is pretty clear.

- I have a key, made up of four fields which are available in both tables, so I can use that key to link the two.

- In the one table, I now have every single day, generated in loops from a start- and end-date.

- In the other table, I have a start- and an end-date as well

=> Now I want to join the two to which end I will use the IntervalMatch() option.

My question on that is: That keyfield I can provide as an additional parameter - does that have to have the same name in both tables already so the tables are linked on that key already?

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Ah  - there is the LOAD statement after the keyword, I had not thought of that - I could use an ALIAS option there to rename the keyfield - what would you suggest? Would it be better to already rename the keyfield beforehand so it is already linked - or to leave the two tables unlinked prior to the INTERVALMATCH operation?

[datanibbler]

Hi,

it seems to work fine now - but I have another problem now: Now I have joined the interval data - so I know that for a certain packing_plan, there was a time valid in that very interval - but I don't have that time 😉 When I include that as an additional field in the LOAD statement following the IntervalMatch() option, it doesn't seem to work anymore ... how could I do that?

Thanks a lot for some help!

Best regards,

DataNibbler

P.S: The integrated Help_file even says that with the advanced syntax, there can be only two columns in the table generated by the LOAD, plus one for every keyfield to be considered ... so how to add another one?

Do I have to keep the other table (and somehow keep it from forming a giant synthetic key) and then use those Intervals to form a unique key that I can use to join or map that field into the main_table?

[Anonymous]

perhaps you can solve it alternatively like that:

Creating Reference Dates for Intervals

I prefer this approach, because it is easier to handle...

[datanibbler]

Hi Robin,

well, what should I say - I am already using something very similar - you see, IntervalMatch() can handle only scenarios where you have intervals on one side and discrete datapoints on the other - I had packing_plans with only á startdate and an enddate to begin with, so I "pumped up" that table with every day of one specific month. Now I have a second table with packing_times valid from a startdate to an enddate - that is where IntervalMatch() comes in.

Of course I could do the same "pumping up" with the packtimes_table and then just join them - but at this point the duration is becoming crucial as next week is my last in this company and I have to get through a lot of this kind until then - after I'm gone, all this will probably be left to itself (QlikView has kind of been lying around on the deathbed for about a year here 😉

Best regards,

DataNibbler

[Anonymous]

I think however you'll solve it either via intervalmatch() or "the other method",

it's always about "pumping up" data, because QlikView matches only on 100%-matches...

[datanibbler]

Hi,

it does work - but now I have a very strange phenomenon: The IntervalMatch() works, I do a "LEFT JOIN Intervalmatch()" with the Key, a start- and an enddate. Both dates are correctly joined into the original table - when I delete the other table afterwards, everything seems fine.

<=> when instead I process the other table (because I still need to join or map the actual data that I need  - the data that is valid between those two dates) and I rename some fields to avoid a synthetic key - the dates seem to be gone from my original table?

I am trying to find out what I am doing wrong here. Maybe someone can give me a hint.

Thanks a lot!

Best regards,

DataNibbler

[datanibbler · Accepted solution]

Ah - I had a look into the integrated help and I found my mistake - it's a scenario I never had before - when using "DROP FIELD", if there is no table_name specified, the field will be dropped from ALL tables where it is included ... well, that can be fixed ...