Comparing columns from different tables and count if

[vizz3108]

I am a newbie in Qlikview. Please help.  Here is my code.

Master:
LOAD * INLINE [
ManagerName, ClientName, Revenue
A, AA, 19
A, AA, 17
A, AC, 9
A, AD, 23
A, AE, 76
B, BA, 12
B, AA, 7
B, AB, 5
C, CA, 8
C, CB, 44
C, CC, 9
, ,
];
Info:
LOAD * INLINE [
ManagerName, Region, Product, Client
A, ASIA, Car, AA
A, ASIA, Car, AA
A, EUROPE, Bike, AC
B, ASIA, Cycle, BA

B, ASIA, Laptop, BA
B, AMERICA, Pen, AA
B, EUROPE, Cap, AB
C, ASIA, Mobile, CA
C, EUROPE, Plane, CB
C, EUROPE, Car, CB
];

Client and ClientName are same fields with different names.

Requirement:

I want to have an output table with ManagerName, Region, Product, ClientCount.  From Table "info", for a given manager, I want to count only those clients who have revenues more than 10. Output table can be in front end using set analysis or on Code as separate table. Either works. 

To make it clearer, This will be the process in Excel.

1. Filter a given Manager name in both tables. 

2. Filter client names in Table "Master" with revenues more than 10. 

3. Take client names more than 10 from above step and apply that as filter in clients column of table "info"

4. Delete other rows in table "info"

5. Repeat above steps for all managers.

6. In the resulting table, take client count grouping for ManagerName, product, region in Table "Info" and delete clients column.

Many Many Thanks in advance!!

[canerkan]

Hi Vizz3108,

try something like this in code:

mapRevenue:
mapping Load
ManagerName & '_' & ClientName as ManagerAndClient,
Sum(Revenue)
Resident Master
Group by ManagerName & '_' & ClientName;

NewTable:
Load
ManagerName & '_' & Region & '_' & Product as MngRegPrd,
Count(Client) as ClientCount
Resident Info
Where ApplyMap('mapRevenue', ManagerName & '_' & Client, 0) > 10
Group by ManagerName & '_' & Region & '_' & Product;

Qualify*;
Final:
Load
Left(MngRegPrd,Index(MngRegPrd, '_')-1) as ManagerName,
TextBetween(MngRegPrd,'_','_') as Region,
Right(MngRegPrd, Len(MngRegPrd)- Index(MngRegPrd,'_',2)) as Product,
ClientCount
Resident NewTable;
Drop Table NewTable;

I have seen, that in Table "Master" one Customer (AA) had two Managers (A, B) and i am not sure if this is correct. I assumed it was correct so i build a new field (ManagerName & '_' & ClientName as ManagerAndClient) in the mapping load to calculate the revenue of this customer for each manager.

I am sure there is a smoother way to do this but at least it is a temporarily fix.

Please double check if the results are correct.

Regards,

Can

[vizz3108]

Many Thanks for the solution. Can you suggest an efficient way to split the key into various fields, since I have many fields in my case?

[canerkan]

Can you give me an example on how many fields are being used and how the key is build?