Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
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
[NewTable]:
Load
Count(incomingserial) - Count(Dispatchedserialno) - Count(closedserial) as Leftover;
[Open]:
LOAD
'Open' as Status,
LOGTIME,
CREATETIME,
ACCEPTTIME,
SERIALNO,
HANDLINGSTAFF,
HANDLINGROLE,
GROUPNAME,
AGENTNAME,
[Case Status],
STATUS,
CALLERNO,
CALLEDNO,
SUBSNUMBER,
CUSTID,
PAYACCCODE,
ACCOUNTCODE,
SUBSCRIBEID,
day_diff,
Year(REPORT_DATE) as Year,
Month(REPORT_DATE) as Month,
Day(REPORT_DATE) as Day,
Date(Daystart(REPORT_DATE), 'DD-MMM') as DATE
FROM
OpenCases.qvd
[Dispatched]:
LOAD
'Dispatched' as Status,
GROUPNAME ,
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
[COUNT(0)]
FROM
Dispatched.qvd
[Slept cases]:
LOAD
'Slept' as Status,
[sleep case] as SERIALNO,
[handling group] as GROUPNAME,
//date,
Date(Daystart(date), 'DD-MMM') as DATE,
// Date(date) as date,
Year(date) as Year,
Month(date) as Month,
Day(date) as Day
FROM
(qvd);
[Closed]:
LOAD
'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
FROM
Closed.qvd
[Incoming]:
LOAD
'Incoming' as Status,
HANDLINGROLE as IncomingHandlingrole,
SERIALNO ,
SERIALNO as incomingserial,
GROUPNAME,
[COUNT(0)] as IncomingCount ,
Year(REPORT_DATE) as Year,
Month(REPORT_DATE) as Month,
Day(REPORT_DATE) as Day,
Date(Daystart(REPORT_DATE), 'DD-MMM') as DATE
FROM
Incoming.qvd
HI.
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.
Ridhaa Hendricks
Data Analyst
0842276595
r.hendricks@cellc.co.za
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
Hi.
One of the strength of QV is the ability to create calculated dimensions.
Use aggr function to build dimension that you want.
Hi
I have tried using a calculated dimension it doesn’t seem to work.
Ridhaa Hendricks
Data Analyst
0842276595
r.hendricks@cellc.co.za
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
Hi.
Could you post your tries ?