5 Replies Latest reply: Apr 9, 2014 3:07 PM by whiteline _ RSS

    Aggregation on 3 different tables

    Ridhaa Hendricks

      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

      [C:\QlikView\QVDS\IPCC_QVDS\Cases\Back Office\Slept Cases.qvd]

      (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