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
maybe something like that :
t:
LOAD RateKey,
%ZTICKNO,
Ticket_Type,
Type_Ind,
BillCode,
ZWDATE,
ZHours
FROM
[TimeTickets.xlsx]
(ooxml, embedded labels, table is Sheet1);
inner join(t)
intervalmatch(ZWDATE,RateKey)
LOAD
StartDate,
EndDate,
RateKey
// Rates
FROM
[Rates.xls]
(biff, embedded labels, table is Sheet1$);
left join(t)
load
StartDate,
EndDate,
RateKey,
Rates
FROM
[Rates.xls]
(biff, embedded labels, table is Sheet1$);
Something like this:
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, RateKey)
load StartDate,EndDate
resident ZTRBLRATES;
Left Join (ZTRHours)
Load Rates,
RateKey,
StartDate,
EndDate
Resident ZTRBLRATES;
Drop table ZTRBLRATES;
shouldn't you load RateKey in
nner Join IntervalMatch (ZWDATE, RateKey)
load StartDate,EndDate
resident ZTRBLRATES;
?
It's tripling my numbers.....
I should be able to create a pivot table and show total cost by %ZTICKNO....
that means you have a problem with the key
are you sure it is unique ?
It's getting sort of confusing.....here is a table dump of all the columns in my detail table. I use the RateKey and ZWDATE has to fall within my StartDate and EndDate on my rates table to give me the correct rate. When I have that I take SHours * Rate to get the extended amount. What I want to be able to do is create a pivot table with %ZTICKNO and extended amount. Does not sound like rocket science (LOL).
Here is the results after the inner join and intervalmatch on ZWDATE. I should only have the ones where the ZWDATE falls between StartDate and EndDate.
I think I got it to work. See what you think.....I added a distinct clause....
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,
RateKey,
ZWDATE,
Type_Ind,
Ticket_Type,
BillCode
;
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;
Left Join (ZTRBLRATES)
Load Distinct *
Resident ZTRHours;
Drop table ZTRHours;
Exit Script;