Is there an alternative to Excel XLOOKUP function in QlikView? (link two tables by timestamp)

SunnyNight — Wed, 21 Sep 2022 12:51:21 GMT

Suppose the
1st table columns with the date and time and the name of the event
2nd table has a column with date and time

In the 1st and 2nd tables, the time may be different.

Need to link the two tables by date and time. The xLookUp function in Excel will do this task. Is there an alternative to the Excel xLookUp function in QlikView?

Examples of tables:

1-st table
Timestamp(table1) Event(table1)
10.09.2022 07:03:11 1.Event
10.09.2022 07:05:32 2.Event
10.09.2022 07:07:35 3.Event
10.09.2022 07:12:51 4.Event
10.09.2022 07:14:41 5.Event

2-nd table
Timestamp(table2)
10.09.2022 07:05:43
10.09.2022 07:07:45
10.09.2022 07:07:46
10.09.2022 07:07:47
10.09.2022 07:07:48
10.09.2022 07:07:49
10.09.2022 07:13:00
10.09.2022 07:14:57
10.09.2022 07:15:00

Expected result:

Timestamp(table2) Event(table1)
10.09.2022 07:05:43 2.Event
10.09.2022 07:07:45 3.Event
10.09.2022 07:07:46 3.Event
10.09.2022 07:07:47 3.Event
10.09.2022 07:07:48 3.Event
10.09.2022 07:07:49 3.Event
10.09.2022 07:13:00 4.Event
10.09.2022 07:14:57 5.Event
10.09.2022 07:15:00 5.Event

Re: Is there an alternative to Excel XLOOKUP function in QlikView? (link two tables by timestamp)

rubenmarin — Wed, 21 Sep 2022 13:19:32 GMT

Hi you can use intervalmatch to search between ranges:https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/IntervalMatch.htm

And to create the ranges you can use Peek to access the previous loaded row, so loading the table sorted by the timestamp descendat, you can access the current row timestamp and the previous timestamp to crete the strat and end range for the current timestamp.

topic Re: Is there an alternative to Excel XLOOKUP function in QlikView? (link two tables by timestamp) in Reporting Service & Alerting

Is there an alternative to Excel XLOOKUP function in QlikView? (link two tables by timestamp)

Re: Is there an alternative to Excel XLOOKUP function in QlikView? (link two tables by timestamp)