topic Re: IntervalMatch. Cannot get it right in QlikView

IntervalMatch. Cannot get it right

MindaugasBacius — Wed, 04 May 2016 09:50:20 GMT

Two tables:

Intervals (15 rows)
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!

Re: IntervalMatch. Cannot get it right

MK_QSL — Wed, 04 May 2016 10:01:43 GMT

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;

Re: IntervalMatch. Cannot get it right

Gysbert_Wassenaar — Wed, 04 May 2016 10:05:13 GMT

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);

Re: IntervalMatch. Cannot get it right

Kushal_Chawda — Wed, 04 May 2016 10:23:17 GMT

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);