Qlikview - Join 2 tables with more keys

[alexandermllr]

Hello,

I have this table:

Key	Time	Place
1	01.01.2018 02:00	2464
1	01.01.2018 05:06	4657
1	02.01.2018 01:45	3578
1	02.01.2018 16:12	8767
1	02.01.2018 19:00	1245

and i need to join this table to the first one:

Key	Place from	Place to	New Field
1	2464	3578	43113
1	8767	1245	44121

So I have the Key field and the place as a "Key" + the time for the sorting.

This should be the result:

Key	Time	Place	New field
1	01.01.2018 02:00	2464	43113
1	01.01.2018 05:06	4657	43113
1	02.01.2018 01:45	3578	43113
1	02.01.2018 16:12	8767	44121
1	02.01.2018 19:00	1245	44121

I hope someone can help me!

Thank you

[Miguel_Angel_Baeyens]

Take a look at these, among others:

help using a 'BETWEEN' join on tables from different datasources

IntervalMatch

Doubt in Interval Match

Re: Intervalmatch

[alexandermllr]

Thank you!

I tried it, but it's not working for my data because the fields "place from" and "place to" are not sorted.

I need to use the "Time" for matching it.

How is it possible?

[marcelviegas]

attempts to concatenate the keys (compound key)

key1&'|'&key2

[alexandermllr]

That's not working with my data.

In my example the Place "4657" would have no new field then, because it's between...

[Miguel_Angel_Baeyens]

The idea of IntervalMatch() is exactly to simplify that exercise that anyway you have to do. If you have missing values (out of range, out of the interval) you should create such intervals in the datamodel, unless those values are really to be dropped.

In your example, you would need to create a new line for the interval from 3759 to 8766, in this case with the value of 43113. I think you have some code examples in the threads I mentioned but if you search the Qlik Community you will find a lot more examples covering other uses cases perhaps more similar to yours.