topic Re: Look for value in range in QlikView

Look for value in range

Wed, 22 Apr 2015 07:01:48 GMT

Hi all.

I atach an excel with example of value and expected table.

Basically i have a table with a date. With that date and id i need look for a value in other table, but the other table has from - to no exaclty the same date.

I atach to, expectec table.

Thanks in advance.

Re: Look for value in range

mato32188 — Wed, 22 Apr 2015 07:21:16 GMT

Hi Federico,

I am not sure, whether I cought your point or not, but looks like you have to use ex. IntervalMatch() function with some join.

Henric created very nice topic about such issue, link: IntervalMatch and Slowly Changing Dimensions

Hope would help

Re: Look for value in range

jonathandienst — Wed, 22 Apr 2015 07:22:25 GMT

This sound like an interval matching problem. Check out interval matching in the reference manual or search here for "interval match"

I suggest that you post a sample of your qvw document for more detailed help.

HTH

Jonathan

Re: Look for value in range

Wed, 22 Apr 2015 08:08:38 GMT

I do it.. but i think will be better my script. This script give expect result. can you give me an advise?

table:
LOAD DateStart,
idCustomer,
DateStart&'|'& idCustomer as key_date_customer
FROM
INFO.xlsx
(ooxml, embedded labels, table is Sheet1);

Intervals:
LOAD Datefrom,
Dateto,
Datefrom& '|'&Dateto as _key,
idCustomer as idCustomer_aux,
QUANTITY
FROM
INFO.xlsx
(ooxml, embedded labels, table is Sheet2);

IntervalMatch_aux:
IntervalMatch (DateStart)
Load distinct Datefrom, Dateto resident Intervals;

left join (Intervals)
LOAD
Datefrom& '|'&Dateto as _key,
DateStart Resident IntervalMatch_aux; drop Table IntervalMatch_aux;
drop fields Datefrom, Dateto, _key from Intervals;

left join(table)
LOAD
num(DateStart)&'|'& idCustomer_aux as key_date_customer,
QUANTITY
Resident Intervals; DROP Table Intervals;
drop Field key_date_customer from table;

Re: Look for value in range

mato32188 — Wed, 22 Apr 2015 08:25:19 GMT

table:
LOAD DateStart,
idCustomer
FROM
INFO.xlsx
(ooxml, embedded labels, table is Sheet1);

Intervals:
LOAD Datefrom,
Dateto,
idCustomer,
QUANTITY
FROM
INFO.xlsx
(ooxml, embedded labels, table is Sheet2);

IntervalMatch_aux:
IntervalMatch (DateStart, idCustomer)
Load distinct Datefrom, Dateto, idCustomer resident Intervals;

New:
LOAD *
RESIDENT Intervals;
drop table table;

left join(New)
LOAD *
Resident Intervals;
drop table intervals;

Sorry, but I have no QlikView desktop right here, created on the fly.

Re: Look for value in range

Anonymous — Wed, 22 Apr 2015 08:49:08 GMT

Hi,

You should use the fonction 'Intervalmatch' in your case:

TABLE1:
LOAD DateStart,
idCustomer
FROM
[.\INFO.xlsx]
(ooxml, embedded labels, table is Sheet1);

TABLE2:
LOAD Datefrom,
Dateto,
idCustomer,
QUANTITY
FROM
[.\INFO.xlsx]
(ooxml, embedded labels, table is Sheet2);

LEFT JOIN(TABLE2)
IntervalMatch(DateStart)
LOAD Datefrom,
Dateto
RESIDENT TABLE2;

LEFT JOIN(TABLE2)
LOAD *
RESIDENT
TABLE1;

DROP TABLE TABLE1;