Discussion Board for collaboration on QlikView Scripting.
Please could you assist , I need to do an Aggregation on 3 tables but need to pull through all of the below tables as well.
I want to count the serialno of incoming,dispatched and closed and subtract them from each other like below
Ive tried doing this using resident loads but I cant seem to do resident loads from multiple tables
I need Leftover to be one of the fields in Status
Count(incomingserial) - Count(Dispatchedserialno) - Count(closedserial) as Leftover;
'Open' as Status,
Year(REPORT_DATE) as Year,
Month(REPORT_DATE) as Month,
Day(REPORT_DATE) as Day,
Date(Daystart(REPORT_DATE), 'DD-MMM') as DATE
'Dispatched' as Status,
Date(Daystart(DISPATCHED), 'DD-MMM') as DATE,
Year(DISPATCHED) as Year,
Month(DISPATCHED) as Month,
Day(DISPATCHED) as Day ,
D_SERIALNO as SERIALNO,
D_SERIALNO as Dispatchedserialno
'Slept' as Status,
[sleep case] as SERIALNO,
[handling group] as GROUPNAME,
Date(Daystart(date), 'DD-MMM') as DATE,
// Date(date) as date,
Year(date) as Year,
Month(date) as Month,
Day(date) as Day
'Closed' as Status,
CL_GROUPNAME as GROUPNAME ,
Year(COMPLETETIME) as Year,
Month(COMPLETETIME) as Month,
Day(COMPLETETIME) as Day,
Date(Daystart(COMPLETETIME), 'DD-MMM') as DATE,
[COUNT(0)] as ClosedCount,
SN as SERIALNO,
SN as closedserial
'Incoming' as Status,
HANDLINGROLE as IncomingHandlingrole,
SERIALNO as incomingserial,
[COUNT(0)] as IncomingCount ,
For script calculation choose the granularity, keys, join the corresponding tables and then use group by and count.
But why don't you want to calculate the Leftover in front-end to be affected by user selections ?
I need to do a pivot using the status as a Dimension
If I do it in the expression I won’t be able to see it in the pivot.
This email and its contents are subject to our email legal notice which can be viewed at http://www.cellc.co.za/dl/cms/downloads/Email_legal_notice.pdf
One of the strength of QV is the ability to create calculated dimensions.
Use aggr function to build dimension that you want.
I have tried using a calculated dimension it doesn’t seem to work.
Could you post your tries ?