topic Re: How to link two fields in same table to another? in App Development

How to link two fields in same table to another?

Anonymous — Fri, 02 Sep 2016 03:17:29 GMT

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!

Re: How to link two fields in same table to another?

ecolomer — Fri, 02 Sep 2016 05:21:19 GMT

See this:

Interval Match Feature/Function

Re: How to link two fields in same table to another?

sunny_talwar — Fri, 02 Sep 2016 12:04:06 GMT

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,

Resident RecordTable;

Re: How to link two fields in same table to another?

Anonymous — Tue, 06 Sep 2016 07:40:35 GMT

Synthetic Key is existed.

Can we avoid it or it must exist?

Re: How to link two fields in same table to another?

sunny_talwar — Tue, 06 Sep 2016 22:27:18 GMT

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,

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‌