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?
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.
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!
