How to link two fields in same table to another?

[Anonymous]

Record:

Date	From	To	Value
1/1/2016	1	2	12
1/2/2016	2	3	12
1/3/2016	3	4	13
1/4/2016	4	5	14

User:

UserId	UserName
1	Peter
2	Mary
3	Ken
4	Sam
5	Alice

How can I link field (From) and (To) to UserID?

Many Thanks!

[ecolomer]

See this:

Interval Match Feature/Function

[sunny_talwar · Accepted solution]

Is this what you are looking to get?

Used IntervalMatch as mentioned by ecolomer‌

Script

RecordTable:

LOAD

    "Date",

    "From",

    "To",

    Value

FROM [lib://Comm]

(html, codepage is 1252, embedded labels, table is @1);

User:

LOAD

    "UserId",

    UserName

FROM [lib://Comm]

(html, codepage is 1252, embedded labels, table is @2);

IntervalMatch:

IntervalMatch(UserId)

LOAD From,

  To

Resident RecordTable;

[Anonymous]

Synthetic Key is existed.

Can we avoid it or it must exist?

[sunny_talwar]

You can avoid the synthetic key by joining.

RecordTable:

LOAD

    "Date",

    "From",

    "To",

    Value

FROM [lib://Comm]

(html, codepage is 1252, embedded labels, table is @1);

User:

LOAD

    "UserId",

    UserName

FROM [lib://Comm]

(html, codepage is 1252, embedded labels, table is @2);

Left Join (User)

IntervalMatch(UserId)

LOAD From,

  To

Resident RecordTable;

Left Join (User)

LOAD *

Resident RecordTable;

DROP Table RecordTable;

But HIC points out that it isn't bad to have a synthetic keyIntervalMatch‌