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]

Hello Thomas, Try:

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)
;

Inner Join IntervalMatch (ZWDATE)

load StartDate,EndDate

resident ZTRBLRATES;

[tmumaw]

It works, but I get SSyn on RateKey and zwdate

[marcelviegas]

tries to concatenate the tables or delete the ones that will not be used.

[marcelviegas]

drop table.

[tmumaw]

Hi Marcel,

This is very close to working.  Thanks for all your help on it.  I have one more question.  How can I calculate the dollar value based on ZRates * ZHours?  Tried different things with no luck.

ZTREQP_EMP:
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
date(floor(weekend(ZW_DateEqp, 0, -1)),'YYYY-MM-DD') as ZWDATE,
ZHOURS.ZTREQP as ZHours
FROM

(qvd);

ZTREMP:
Concatenate
LOAD Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
date(floor(weekend(ZW_DateEmp, 0, -1)),'YYYY-MM-DD') as ZWDATE,
ZHOURS.ZTREMP as ZHours
FROM

(qvd);


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

(qvd)
;

Inner Join IntervalMatch (ZWDATE)

load StartDate,EndDate

resident ZTRBLRATES;

[marcelviegas]

I believe that multiplying the value (dollar) by ZHours

[marcelviegas]

What did you try to do?

[tmumaw]

Here is what I have done so far:

ZTREQP_EMP:
LOAD
Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
date(floor(weekend(ZW_DateEqp, 0, -1)),'YYYY-MM-DD') as ZWDATE,
ZHOURS.ZTREQP as ZHours
FROM

(qvd);

ZTREMP:
Concatenate
LOAD
Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
Ticket_Type & Type_Ind & BillCode as RateKey,
date(floor(weekend(ZW_DateEmp, 0, -1)),'YYYY-MM-DD') as ZWDATE,
ZHOURS.ZTREMP as ZHours
FROM

(qvd);

ZTRHours:
Load
Ticket_Type,
%ZTICKNO,
Type_Ind,
BillCode,
RateKey,
ZWDATE,
Sum(ZHours) as SHours
Resident ZTREQP_EMP
Group by
%ZTICKNO,
Type_Ind,
Ticket_Type,
BillCode,
RateKey,
ZWDATE
;

Drop Table ZTREQP_EMP
;

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

(qvd)
;

Inner Join IntervalMatch (ZWDATE)

load StartDate,EndDate



resident ZTRBLRATES;

Exit Script;

Here is what I got out of it.

Then I tried to create a straight table and got this when I took SHours * Rates:

Then I added Bill_Code to it and got the right answer, but I don't want to have to include Bill_Code.