topic Re: intervalMatch problem in QlikView

intervalMatch problem

Anonymous — Wed, 01 Oct 2014 14:18:54 GMT

Hi Guys,

I have two tables: One table that contains the orderDate. And another table that has the ContractStartDate and ContractEndDate

When I do a left join I see the orderDate, ContractDate and ContractEndDate.

Now, when I use IntervalMatch to build the dates between, I null values.

I really would like to know, why is it that the IntervalMatch does not work?

Since an Order can have more than 1 contractdates, I used min and max functions to create 1 line per contract/supplier.

Please have a look at my script.

Looking forward for the solution .

Regards,

Re: intervalMatch problem

Wed, 01 Oct 2014 15:10:52 GMT

Hello your script is wrong. Firstly, we can't use the Left Join on the IntervelMatch function.

I modfied the script and find the attached qvw.

TMP2_Facts:

LOAD contractCompliant.KEY,

supplierId,

productcategoryId,

contractId,

Orderdate.KEY

FROM FACTtable.qvd (qvd);

TEMP_BRIDGE:

IntervalMatch(Orderdate.KEY,contractCompliant.KEY)

Load

numStartDate,

numEndDate,

contractCompliant.KEY

;

LOAD contractCompliant.KEY,

Min(ContractStartDate) as numStartDate,

Max(ContractEndDate) as numEndDate

FROM Contract.qvd (qvd) Group by contractCompliant.KEY;

LEFT Join (TMP2_Facts)

LOAD * Resident TEMP_BRIDGE;

DROP Table TEMP_BRIDGE ;

Re: intervalMatch problem

Anonymous — Wed, 01 Oct 2014 15:26:45 GMT

Hi,

Your solution does not work. .

Both Contractdates are empty.

Cheers

Sam

Re: intervalMatch problem

Wed, 01 Oct 2014 18:29:15 GMT

The reason why I got null value is the the OrderDate is 30-01-2014 and StartDate & Enddate are 01-01-2010 and 01-01-2014. The OrderDate is out of start & end date. so the values are null.

Re: intervalMatch problem

Anonymous — Tue, 14 Oct 2014 12:50:46 GMT

Thanks! I did not pay attention to the orderdate. my mistake. again thanks alot!!!