Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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.
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 ;
Hi,
Your solution does not work. .
Both Contractdates are empty.
Cheers
Sam
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.
Thanks! I did not pay attention to the orderdate. my mistake. again thanks alot!!!