intervalMatch problem

[Anonymous]

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,

[#2]

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 ;

[Anonymous]

Hi,

Your solution does not work. .

Both Contractdates are empty.

Cheers

Sam

[#4 · Accepted solution]

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.

[Anonymous]

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