Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rido1421
Creator III
Creator III

Aggregation on 3 different tables

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

5 Replies
whiteline
Master II
Master II

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 ?

rido1421
Creator III
Creator III
Author

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

whiteline
Master II
Master II

Hi.

One of the strength of QV is the ability to create calculated dimensions.

Use aggr function to build dimension that you want.

rido1421
Creator III
Creator III
Author

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

whiteline
Master II
Master II

Hi.

Could you post your tries ?