Calculation after Intervalmatch

[tmumaw]

Good morning everyone,

Need a little help with my script.  I'm trying to calculate total based on SHours * Rates in a chart and it's not working.  I don't know if it's because of my intervalmatch / inner join or not.  I see my total hour (SHours), but when I try and do a calculation I get nothing.  Thanks for your help.  Here is my script.

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
%ZTICKNO,
Type_Ind,
BillCode,
RateKey,
ZWDATE,
Sum(ZHours) as SHours
Resident ZTREQP_EMP
Group by
%ZTICKNO,
Type_Ind,
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;

[olivierrobin]

in ZTRBLRATES you have the columns and the key RateKey

so you can join it to ZTRHours

[tmumaw]

So here is my new script and the answers I get don't make since.

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;

Left Join (ZTRBLRATES)
Load *
Resident ZTRHours;
Drop table ZTRHours;

Drop Fields Type_Ind,
Ticket_Type,
BillCode,
StartDate,
EndDate, 
RateKey;

Final:
Load *,
Rates * SHours as ExtendedAmt
Resident ZTRBLRATES;
DROP Table ZTRBLRATES;


Exit Script;

Then I remove the date column to just get a total by %ZTICKNO and I get this:

Any ideas?

Thanks

[olivierrobin]

i think the join should be done opposite way

left join(ztrhours)

in your load, you have to precise ONLY the keys needed for the join, and the columns you want to add

[Anonymous]

Hi Thomas,

Can you provide Sample data ?

[tmumaw]

So what you are saying is only do left join and load key and rates?

[olivierrobin]

that's it

[tmumaw]

Here is some sample data

[Anonymous]

I couldn't see any Hours information, in the excels provided above, can you check once? also please provide your expected result

[tmumaw]

Hours is the last column on the spreadsheet ZHours

[Anonymous]

What is your expected Result?