topic Re: Count(distinct Area_Manager) in QlikView

Count(distinct Area_Manager)

qlikviewforum — Sun, 04 May 2014 11:20:22 GMT

Hi,

Imagine I’m using Area Code in the Dimension which is getting loaded from one table and I’m using Count(Distinct Area Manager) in the expression. In my case as the data is huge it is throwing “Object out of Memory” error. So what I thought was is there is any way calculate the expression in the script level itself so that it is faster. In the end basically I wanted to know if there is more than one Area Manager is assigned to same Area Code. If the Count(Distinct Area Manager) > 2 then I want to fetch only those records in the level so that it is faster.

In my case Area Code is in different table which is connected to FACT table with its key field and Area Manager is in different table which is again connected to FACT table using the key field. So any idea how we can get this in script level itself?

Please let me know if you have any questions

Thanks in advance!

Re: Count(distinct Area_Manager)

swuehl — Sun, 04 May 2014 11:39:55 GMT

You can do the aggregation in the script using a GROUP BY clause, or maybe using something like

INPUT:

LOAD * INLINE [

AreaCode, AreaManager

A, Steve

B, Steve

A, Pete

C, Dave

];

TMP:

LOAD *, Exists(DupeCode) as Dupe;

LOAD AreaCode as DupeCode, AreaCode

Resident INPUT;

DUPES:

LOAD DISTINCT AreaCode, Dupe Resident TMP where Dupe;

drop table TMP;

Re: Count(distinct Area_Manager)

jmmayoral3 — Sun, 04 May 2014 12:00:04 GMT

IF you have 3 tables in a transactional database, you can do a query to recover data and be the database server who does the work instead of Qlikview.

You can do this Query.

Select AreaCodeTable.AreaCode_ID,

AreaManagerTable.AreaManager_ID

From AreaManagerTable,

AreaCodeTable,

FactTable

Where AreaManagerTable.AreaManager_ID = FactTable.AreaManager_ID

and AreaCodeTable.Areacode_ID = FactTable.AreaCode_ID

and AreaCodeTable.AreaCode_ID IN

( select A.AreaCode_ID

From AreaManagerTable as B,

AreaCodeTable as A,

FactTable as C

Where B.AreaManager_ID = C.AreaManager_ID

and A.Areacode_ID = C.AreaCode_ID

Group by A.AreaCode_ID

Having (Count( distinct B.AreaManager_ID) > 1

);

Pay attention that the nested query has his tables renamed. Nested query give you the IDs for Area codes with more than one Manager.

The external query give you IDs of Area codes and IDs of Managers, but only for IDs of Areas recovered in nested query. If you want more information (Area name, Manager, name....) you can add that columns in external Query.

Re: Count(distinct Area_Manager)

qlikviewforum — Sun, 04 May 2014 16:01:00 GMT

Thanks Swuehl

I will check that out. Can you please show one example using a Group By?

Re: Count(distinct Area_Manager)

qlikviewforum — Sun, 04 May 2014 16:02:55 GMT

Thanks Jose

Can you please tell me how it can done in QVW instead of doing it in DB server. As we are using the common QVS I'm not supposed to change it. So please suggest!

Re: Count(distinct Area_Manager)

swuehl — Sun, 04 May 2014 16:57:16 GMT

Something like

INPUT:

LOAD * INLINE [

AreaCode, AreaManager

A, Steve

B, Steve

A, Pete

C, Dave

];

DUPES:

LOAD AreaCode,

if(count( DISTINCT AreaManager)>1, 1,0 ) as Dupe,

count(DISTINCT AreaManager) as Count

Resident INPUT group by AreaCode;