Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Take a look at these, among others:
help using a 'BETWEEN' join on tables from different datasources
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?
attempts to concatenate the keys (compound key)
key1&'|'&key2
That's not working with my data.
In my example the Place "4657" would have no new field then, because it's between...
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.