Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(
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
(
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
(
;
Inner Join IntervalMatch (ZWDATE)
load StartDate,EndDate
resident ZTRBLRATES;
Exit Script;
hello
are you sure all your date columns are in the same format ?
Yes. I just need to be able to calculate the extended Rate (SHours * Rates) in the script.
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
Already tried that.
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
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
(
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
(
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
(
;
Inner Join IntervalMatch (ZWDATE)
load StartDate,EndDate
resident ZTRBLRATES;
Exit Script;
create a new table
toto:
load ......
SHours * Rates as NewField
resident ......
and drop your 1st table
Which resident table? ZTRBLRATES or ZTRHours
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
(
;
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;