How to join after intervalmatch() has been used

[#1]

Hello,
I'm trying to connect my PurchaseOrder table with my Invoices table using intervalmatch (extended).
The intervalmatch part is working and I manage to match the invoice date to the correct purchase order intervall.

My problem is that I would like to transfer the rest of the data in my PurchaseOrder table to my Invoice table (to avoid syn key problems).

The join used below results in all combinations of purchase order data per invoice.
I.e. in the example below the 2nd and 3rd row do not belong to the interval 15/2 - 15/3.

E.g.
InvoiceNumber, ArtikelNr, Inv_Date, DATFRAN, DATTILL, INKPR, VALUKOD, VALUKURS
1 , A , 20110301,20110215, 20110315, 89, USD, 6,5
1 , A , 20110301,20110215, 20110315, 85, USD, 6,4
1 , A , 20110301,20110215, 20110315, 80, USD, 6,7

The desired result is as follows:
InvoiceNumber, ArtikelNr, Inv_Date, DATFRAN, DATTILL, INKPR, VALUKOD, VALUKURS
1 , A , 20110301,20110215, 20110315, 89, USD, 6,5

I.e. the Purchase Order row that "belongs" to the invoice

Here is my script:
PurchaseOrder_intervall:
LOAD LAGARTID as ArtikelNr,
DATFRAN,
DATTILL,
INKPR,
VALUKOD,
VALUKURS
FROM
Purchase_Order_Interval.qvd (qvd)
where LAGARTID > 1000000000;

Invoices:
LOAD
InvoiceNumber,
ItemNumber as ArtikelNr,
Price,
Rate,
date(TimeStampUpdated) as Inv_Date
FROM
HY_Invoice_Row.qvd (qvd);

//Joined:
INNER JOIN INTERVALMATCH(Inv_Date, ArtikelNr)
LOAD
DATFRAN,
DATTILL,
ArtikelNr
RESIDENT PurchaseOrder_intervall;

/* This part does not work the way I want it to
left join(Fakta)
load
Key,
ArtikelNr,
INKPR,
VALUKOD,
VALUKURS
Resident IO_intervall;
*/

DROP TABLE IO_intervall;

Anyone know hos to solve this?

br
Martin

[#2 · Accepted solution]

Hi Martin,

Problem with interval match is that it is pretty much a preceding load statement, so a left join cannot be used on the load statement, the only way around this is by doing a resident reload of the table and left joining this one to the table you want to.

I.e.

left join(Table1)

intervalmatch(Date)

StartDate,

EndDate

From Wherever;

The above will NOT work.

2 ways of getting rid of a synthetic key on interval match, either by doing the above, i.e. reloading the table with the interval matched field and preceding it with left join (to the table name you want to join), otherwise the "out of the book" technique for preventing synkeys is along the lines of the following:

1. Simple intervalmatch:

OrderPeriod:
LOAD * INLINE [
StartDate, EndDate, PeriodName
01/08/2008, 07/08/2008, 'AugustPeriod'
01/08/2008, 20/08/2008, 'AugustExtendedPeriod'
15/02/2008, 15/03/2008, 'FebToMarchPeriod'
01/06/2008, 08/12/2008, '2ndHalfYearPeriod'
];

OrderPeriod:
LOAD *,
StartDate & '-' & EndDate as PeriodKey;
LOAD * INLINE [
StartDate, EndDate, PeriodName
01/08/2008, 07/08/2008, 'AugustPeriod'
01/08/2008, 20/08/2008, 'AugustExtendedPeriod'
15/02/2008, 15/03/2008, 'FebToMarchPeriod'
01/06/2008, 08/12/2008, '2ndHalfYearPeriod'
];

PeriodMatchTemp:
IntervalMatch(Date)
LOAD StartDate, EndDate RESIDENT OrderPeriod;

// Solution to eliminate synthetic keys

PeriodMatch:
LOAD
StartDate & '-' & EndDate AS PeriodKey,
Date
RESIDENT PeriodMatchTemp;

DROP TABLE PeriodMatchTemp;

2. Extended interval match (with slowly changing dim):

OrderPeriod:
LOAD * INLINE [
StartDate, EndDate, PeriodName
01/08/2008, 07/08/2008, 'AugustPeriod'
01/08/2008, 20/08/2008, 'AugustExtendedPeriod'
15/02/2008, 15/03/2008, 'FebToMarchPeriod'
01/06/2008, 08/12/2008, '2ndHalfYearPeriod'
];

Region:
LOAD *,
RegionNo & '-' & ValidFrom & '-' & ValidTo as DimKey;
LOAD * INLINE [
RegionNo, ValidFrom, ValidTo, RegionName
1, 01/08/2008, 07/08/2008, 'North'
1, 08/08/2008, 10/08/2008, 'GreaterNorth'
2, 15/02/2008, 15/03/2008, 'West'
3, 01/06/2008, 08/12/2008, 'East'
];

DimMatchTemp:
IntervalMatch (Date,RegionNo) Load ValidFrom, ValidTo, RegionNo resident Region;

// Solution to remove synthetic keys

//INNER JOIN (OrderItemFact)

LinkTable:
LOAD
RegionNo & '-' & ValidFrom & '-' & ValidTo AS DimKey,
RegionNo & '-' & Date AS DimRef
RESIDENT DimMatchTemp;

OrderItemFact2:
LOAD *,
RegionNo & '-' & Date AS DimRef RESIDENT OrderItemFact;

DROP FIELD RegionNo FROM OrderItemFact2;
DROP TABLE DimMatchTemp, OrderItemFact;

Hope these sample scripts help.

[#3]

Hello,

Thank you for your reply.
I have read it a couple of times now and I have a hard time following.
In your example where does Date come from?
"IntervalMatch (Date,RegionNo)"

If I try to translate your solution to my script and get the following:

IO_intervall:
LOAD LAGARTID as ArtikelNr,
DATFRAN,
DATTILL,
INKPR,
VALUKOD,
VALUKURS
FROM
D:\QlikViewSystems\PCD\Harmoney\2_Transform\HY_Purchase_Order_Interval.qvd
(qvd)
where LAGARTID > 1000000000;

Fakta:
LOAD
InvoiceNumber,
ItemNumber as ArtikelNr,
Price,
Rate,
date(TimeStampUpdated) as Inv_Date
FROM
D:\QlikViewSystems\PCD\Harmoney\2_Transform\HY_Invoice_Row.qvd (qvd)
where ItemNumber > 1000000000;

DimMatchTemp:
IntervalMatch (Inv_Date, ArtikelNr) Load DATFRAN, DATTILL, ArtikelNr resident IO_intervall;

LinkTable:
LOAD
ArtikelNr & '-' & DATFRAN & '-' & DATTILL AS DimKey,
ArtikelNr & '-' & Inv_Date AS DimRef
RESIDENT DimMatchTemp;

OrderItemFact2:
LOAD *,
ArtikelNr & '-' & Inv_Date AS DimRef RESIDENT LinkTable;

DROP FIELD ArtikelNr FROM OrderItemFact2;
DROP TABLE DimMatchTemp, OrderItemFact;

When I load the data I get an error message when I get to
OrderItemFact2:
LOAD *,
ArtikelNr & '-' & Inv_Date AS DimRef RESIDENT LinkTable;

The field ArtikelNr can not be found.

Do you know what I am doing wrong?

Also I do not see how this helps me transfer INKPR, VALUKOD and VALUKURS to the table with Invoice information. Could you please try explaining and showing once more?
Br
Martin

[#4]

Hi martnorm or CheenuJanakiram.

Would it be possible to post the final code?

I also have problems understanding where date comes from.

Thanks in advance!

Renco