Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Calculation after Intervalmatch

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;

27 Replies
Highlighted
Specialist III
Specialist III

in ZTRBLRATES you have the columns and the key RateKey

so you can join it to ZTRHours

Highlighted
Specialist
Specialist

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

Highlighted
Specialist III
Specialist III

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

Highlighted
Contributor III
Contributor III

Hi Thomas,

Can you provide Sample data ?

Highlighted
Specialist
Specialist

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

Highlighted
Specialist III
Specialist III

that's it

Highlighted
Specialist
Specialist

Here is some sample data

Highlighted
Contributor III
Contributor III

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

Highlighted
Specialist
Specialist

Hours is the last column on the spreadsheet ZHours

Highlighted
Contributor III
Contributor III

What is your expected Result?