Matching Logic

[tmumaw]

Good morning everyone,

I have a question to ask everyone.  I have 2 QVD's one has detailed information by PO Type, PO Key, Billing Code and date, then the other QVD(Pricing) has PO type, PO key, Billing code and date.  The problem is the QVD(Pricing) can have multiple prices for different price range dates.  I need to match my detail file with my pricing file and be sure and use the correct price to that time period.  Hope this makes since.  Thanks

[marcelviegas]

try,

wildmatch

[Anil_Babu_Samineni]

Can you explain little more to check?

[tmumaw]

I will try.  I have a Orders file which has Order number and products to order and the order date.  On the other file I have my pricing by product, date and price.  The pricing file can have multiple entries for the same product only for different dates.  I have to match the key from the orders with the key from the pricing based on dates.  Ex.

Order  xyz

product 123

date 7/20/2018

Pricing

Product     from date    to date       price

123            6/30/2018  7/5/2018      100.00

123            7/6/2018    7/15/2018       98.00

123            7/16/2018  7/25/2018     150.00        (I need to use this one)

Hope this helps.

Thanks

[marcelviegas]

IntervalMatch

[YoussefBelloum]

Hi,

What is the rule that makes you choose that line ?

You can try to use intervalmatch to match your Order date (from the Order table) to your Pricing Date fields (From_Date, To_Date)..

[tmumaw]

The product from the order date fill within from and to date range.

Order  xyz

product 123

date 7/20/2018

Pricing

Product    from date    to date      price

123            6/30/2018  7/5/2018      100.00

123            7/6/2018    7/15/2018      98.00

123            7/16/2018  7/25/2018    150.00       (I need to use this one)

[mdmukramali]

Hi Thomas,

As suggested by others Try Interval match Function,

//Like:

Product:

Load * Inline

[

Order,Product,Date

xyz,123,7/20/2018

xyz,123,6/30/2018

];

Price_Table:

Load * inline

[

Product,from_date, to_date,price

123,6/30/2018,7/5/2018,100.00

123,7/6/2018,7/15/2018,98.00

123,7/16/2018,7/25/2018,150.00

];

Inner Join //(Product)

IntervalMatch (Date,Product)

LOAD from_date, to_date, Product

Resident Price_Table;

[tmumaw]

Hi Mohammed,

Been out of the office for a few days.  Here is my script.  How would I add the intervalmatch to it?  The bottom table ZTRBLRATES are the different rates based on ZWDATE from above tables....Thanks

ZTREQP_EMP:
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
ZWDATE.ZTREQP as ZWDATE
FROM

(qvd);

ZTREMP:
Concatenate
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
ZWDATE.ZTREMP as ZWDATE
FROM

(qvd);

ZTRBLRATES:
LOAD TICKCLIENT.ZTRBLRATES,
EMPEQP.ZTRBLRATES,
BILLCODE.ZTRBLRATES,
TICKCLIENT.ZTRBLRATES & EMPEQP.ZTRBLRATES & BILLCODE.ZTRBLRATES as RateKey,
StartDate,
EndDate,
BILLRATE.ZTRBLRATES
FROM

(qvd)
; 

[tmumaw]

Okay Marcel,

Here is my script and I sure can't figure out the intervalmatch.......maybe you can help.

ZTREQP_EMP:
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
ZW_DateEqp as ZWDATE
FROM

(qvd);

ZTREMP:
Concatenate
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
ZW_DateEmp as ZWDATE
FROM

(qvd);

ZTRBLRATES:
LOAD TICKCLIENT.ZTRBLRATES,
EMPEQP.ZTRBLRATES,
BILLCODE.ZTRBLRATES,
TICKCLIENT.ZTRBLRATES & EMPEQP.ZTRBLRATES & BILLCODE.ZTRBLRATES as RateKey,
StartDate,
EndDate,
BILLRATE.ZTRBLRATES
FROM

(qvd)
;


Final_Table:
IntervalMatch(ZWDATE,RateKey)
Left Join (ZTREQP_EMP)
Load
StartDate,
EndDate,
BILLRATE.ZTRBLRATES
Resident ZTRBLRATES;

Left Join (ZTREQP_EMP)
Load Distinct *
Resident ZTRBLRATES;

Drop table ZTRBLRATES; 

ZWDATE does not seem to fall within the start and end date ranges.

Thanks