topic Re: Aggregation on 3 different tables in QlikView

Aggregation on 3 different tables

rido1421 — Wed, 09 Apr 2014 05:32:01 GMT

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

Re: Aggregation on 3 different tables

whiteline — Wed, 09 Apr 2014 05:46:32 GMT

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 ?

Re: Aggregation on 3 different tables

rido1421 — Wed, 09 Apr 2014 05:58:58 GMT

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

Re: Aggregation on 3 different tables

whiteline — Wed, 09 Apr 2014 13:54:46 GMT

Hi.

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

Use aggr function to build dimension that you want.

Re: Aggregation on 3 different tables

rido1421 — Wed, 09 Apr 2014 14:04:13 GMT

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

Re: Aggregation on 3 different tables

whiteline — Wed, 09 Apr 2014 19:07:11 GMT

Hi.

Could you post your tries ?