How to filter it?

[#1]

Hi,

I have two table, and I store it into QVD.

Test data:

Order:
load * Inline
[
OrderId,MemberId,StartDate,EndDate
1,1,2010/01/03,2010/09/01
2,1,2010/10/01,2010/12/01

];

Usage:
load * Inline
[
MemberId,CompletionDate
1, 2010/05/01
1,2010/11/01
]

But now I just want to load Usage, but I need to attach the relevant OrdID to the Usage, that is judged by the MemberId and whether the CompletionDate is between the relevant startdate and enddate.

For this situation, the results look like this:

OrderId MemberId CompletionDate

1, 1, 2010/05/01

2, 1, 2010/11/01

How to use script to load it?

Thanks.

[pat_agen · Accepted solution]

hi,

try the following

// load usage
loadUsage:
load
Memberid,
CompletionDate
from usage.qvd (qvd);

// load Order but only for Membrids present in Usage file
Order:
load
OrderId,
MemberId,
Startdate,
EndDate
from Order.qvd (qvd)
where exists(MemberId,MemberId);
;

// join the two tables together
loadUsage:
join (loadUsage)
load
Memberid,
OrderId,
Startdate,
EndDate
resident Order;

// now reduce this table to eliminate memberIds failing the date test

Usage:
noconcatenate load
Memberid,
OrderId,
CompletionDate
resident loadUsage
where CompletionDate >= StartDate and CompletionDate <= EndDate;

drop tables loadUsage, Order;

this should get you a table called Usage with 3 columns and data respecting your requirements.
OrderId,
MemberId,
CompletionDate

hope this helps

[#3]

Have a look at the IntervalMatch function, Probably the extended syntax. you will end up with something like this:

IntervalMatch ( CompletionDate, MemberId ) select StartDate , EndDate, MemberId from Order;

Jon