Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Look for value in range

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.

5 Replies
mato32188
Not applicable

Re: Look for value in range

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

M

jontydkpi
Not applicable

Re: Look for value in range

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Look for value in range

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;

mato32188
Not applicable

Re: Look for value in range

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.

M

sbobbyraj
Not applicable

Re: Look for value in range

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;