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]

hello

are you sure all your date columns are in the same format ?

[tmumaw]

Yes.  I just need to be able to calculate the extended Rate (SHours * Rates) in the script.

[olivierrobin]

as you have start and end date in your tables, you can left join ZTRBLRATES.QVD on keys start date, end date

so you have all the data on the same row

[tmumaw]

Already tried that.

[olivierrobin]

and the 2 fields are present

if yes, you can either

compute in a graph

or add a piece of script reloading you table with a new field resulting of the multiplication

[tmumaw]

Where in this script would you put the calculation of SHours * Rates?  It has to be after the intervalmatch.

Thanks for your help.

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;

[olivierrobin]

create a new table

toto:

load ......

SHours * Rates as NewField

resident ......

and drop your 1st table

[tmumaw]

Which resident table?  ZTRBLRATES or ZTRHours

[tmumaw]

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;

ZTRHours_Temp:
Load *,
SHours * Rates as ExtendedAmt   <+++++++++ I added this and Rates are not part of the table.....
Resident ZTRHours;
Drop table ZTRHours;
Exit Script;