Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rido1421
Contributor 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
Honored Contributor II

Re: Aggregation on 3 different tables

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
Contributor III

Re: Aggregation on 3 different tables

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
Honored Contributor II

Re: Aggregation on 3 different tables

Hi.

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

Use aggr function to build dimension that you want.

rido1421
Contributor III

Re: Aggregation on 3 different tables

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
Honored Contributor II

Re: Aggregation on 3 different tables

Hi.

Could you post your tries ?