topic Re: Calculation after Intervalmatch in QlikView

Calculation after Intervalmatch

tmumaw — Thu, 26 Jul 2018 13:17:37 GMT

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;

Re: Calculation after Intervalmatch

olivierrobin — Thu, 26 Jul 2018 13:40:43 GMT

hello

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

Re: Calculation after Intervalmatch

tmumaw — Thu, 26 Jul 2018 13:44:21 GMT

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

Re: Calculation after Intervalmatch

olivierrobin — Thu, 26 Jul 2018 15:43:10 GMT

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

Re: Calculation after Intervalmatch

tmumaw — Thu, 26 Jul 2018 15:49:32 GMT

Already tried that.

Re: Calculation after Intervalmatch

olivierrobin — Thu, 26 Jul 2018 15:56:29 GMT

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

Re: Calculation after Intervalmatch

tmumaw — Thu, 26 Jul 2018 16:02:33 GMT

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;

Re: Calculation after Intervalmatch

olivierrobin — Thu, 26 Jul 2018 16:07:22 GMT

create a new table

toto:

load ......

SHours * Rates as NewField

resident ......

and drop your 1st table

Re: Calculation after Intervalmatch

tmumaw — Thu, 26 Jul 2018 17:02:47 GMT

Which resident table? ZTRBLRATES or ZTRHours

Re: Calculation after Intervalmatch

tmumaw — Thu, 26 Jul 2018 17:07:02 GMT

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;

Re: Calculation after Intervalmatch

olivierrobin — Fri, 27 Jul 2018 07:51:50 GMT

in ZTRBLRATES you have the columns and the key RateKey

so you can join it to ZTRHours

Re: Calculation after Intervalmatch

tmumaw — Fri, 27 Jul 2018 11:28:47 GMT

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

Re: Calculation after Intervalmatch

olivierrobin — Fri, 27 Jul 2018 11:59:24 GMT

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

Re: Calculation after Intervalmatch

Anonymous — Fri, 27 Jul 2018 12:04:28 GMT

Hi Thomas,

Can you provide Sample data ?

Re: Calculation after Intervalmatch

tmumaw — Fri, 27 Jul 2018 12:08:11 GMT

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

Re: Calculation after Intervalmatch

olivierrobin — Fri, 27 Jul 2018 12:14:46 GMT

that's it

Re: Calculation after Intervalmatch

tmumaw — Fri, 27 Jul 2018 12:21:18 GMT

Here is some sample data

Re: Calculation after Intervalmatch

Anonymous — Fri, 27 Jul 2018 12:31:54 GMT

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

Re: Calculation after Intervalmatch

tmumaw — Fri, 27 Jul 2018 12:39:11 GMT

Hours is the last column on the spreadsheet ZHours

Re: Calculation after Intervalmatch

Anonymous — Fri, 27 Jul 2018 12:55:23 GMT

What is your expected Result?