IntervalMatch. Cannot get it right

[MindaugasBacius]

Two tables:

1. Intervals (15 rows)
2. Data (31 rows)

I wish to connect Intervals to Data via Data.Day field and the Key value.

The result should still have 31 rows.

Thank you!

[MK_QSL · Accepted solution]

Input:
LOAD Key, 
     Date, 
     Day
FROM
[160504.xlsx]
(ooxml, embedded labels, table is Data);


Interval:
LOAD From, 
     To, 
     Key, 
     Value
FROM
[160504.xlsx]
(ooxml, embedded labels, table is Intervals);


Inner Join 
IntervalMatch(Day, Key)
Load From, To, Key Resident Interval;


Left Join (Input) Load * Resident Interval;

Drop Table Interval;

Drop Fields From,To;

[Gysbert_Wassenaar]

You need to use the Extended Syntax option and use Key as the second parameter:

LOAD Key,
    Date,
    Day
FROM
[160504.xlsx]
(ooxml, embedded labels, table is Data);

LEFT JOIN (Data)

IntervalMatch(Day,Key)
LOAD [From],
    [To],
    Key
FROM
[160504160504.xlsx.xlsx]
(ooxml, embedded labels, table is Intervals);

LEFT JOIN (Data)

LOAD [From],
    [To],
    Key,
    Value
FROM
[160504160504.xlsx.xlsx]
(ooxml, embedded labels, table is Intervals);

[Kushal_Chawda]

Interval:

LOAD

    [From],

    [From]+iterno()-1 as Day

    [To],

   Value,

    Key

FROM

[160504.xlsx]

(ooxml, embedded labels, table is Intervals)

while [From]+iterno()-1<=[To] ;

inner join (Interval)

LOAD

    Key,

    Date,

    Day

FROM

[160504.xlsx]

(ooxml, embedded labels, table is Data);