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

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

[tmumaw]

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

(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, RateKey)

load StartDate,EndDate

resident ZTRBLRATES;

Left Join (ZTRHours)
Load Rates,
RateKey,
StartDate,
EndDate 
Resident ZTRBLRATES;
Drop table ZTRBLRATES;

[olivierrobin]

shouldn't you load RateKey in

nner Join IntervalMatch (ZWDATE, RateKey)

load StartDate,EndDate

resident ZTRBLRATES;

?

[tmumaw]

It's tripling my numbers.....

[tmumaw]

I should be able to create a pivot table and show total cost by %ZTICKNO....

[olivierrobin]

that means you have a problem with the key

are you sure it is unique ?

[tmumaw]

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.

[tmumaw]

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

(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,
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

(qvd)
;

Inner Join IntervalMatch (ZWDATE)

load StartDate,EndDate

resident ZTRBLRATES;

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

Exit Script;