topic Comparing columns from different tables and count if in App Development

Comparing columns from different tables and count if

vizz3108 — Tue, 30 Nov 2021 18:17:33 GMT

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

Re: Comparing columns from different tables and count if

canerkan — Thu, 27 May 2021 10:45:23 GMT

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

Re: Comparing columns from different tables and count if

vizz3108 — Fri, 28 May 2021 16:32:41 GMT

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?

Re: Comparing columns from different tables and count if

canerkan — Mon, 31 May 2021 06:43:58 GMT

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