Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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;
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
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;