Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sunil-kumar5
Creator II
Creator II

Customer Base

Hi All,

I have two tables and main column are below.

Customer Calls(Total record 1700000) -   PartnerName(Who installed LicenseNumber) , LicenseNumber,Phonenumbercustomer,Calldate (When customer calls related to LicenseNumber query)

PartnerTable (Total Records - 300000)- PartnerName(Who installed LicenseNumber), LicenseNumber, InstallmentDate (LicenseNumber Installment date)

Required Result as below table and Top 10 Partner Name is based on Customer Calls table.

Actually I am getting same result in both the table, even I have tried using flag in both the table, I have also tried group by in the load script and sucessfully got it however Year and Month filter not working.

Top 10 Partner Name Count of LicenseNumber from PartnerTable LicenseNumber Count from CustomerCalls Table
A 3456 234
B 2175 578
C 4000 541
M 5130 451
N 5615 398
F 6752 326
R 4774 297
W 1838 270
I 3016 256
J 910 256

 

Thanks

Labels (6)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

sorry my bad

 

= aggr(

if(rank(count(LicenseNumber_c),4,2)<=10,PartnerName_p)

,PartnerName_p)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

5 Replies
GeorgePhilips23
Partner - Contributor III
Partner - Contributor III

Try this.

Customer Calls:

Load PartnerName as Partner,

LicenseNumber as License,

PartnerName&'-'& LicenseNumber as Key,

Phonenumbercustomer,

Calldate

From.

PartnerTable:

Load 

PartnerName ,

LicenseNumber ,

PartnerName&'-'& LicenseNumber as Key,

InstallmentDate

From.

 

 

 

sunil-kumar5
Creator II
Creator II
Author

Actually , I have mentioned the same name in my questions however they are different following the way you mentioned . I have already tried the same script but success.

Please advise other solutions.

Thanks

vinieme12
Champion III
Champion III

Never use keyfields in calculations or dimensions to avoid ambiguity

create a new column with a different name, so you know exactly which field from which table will be evaluated

 

Customer:

Load

PartnerName

,PartnerName as PartnerName_c

,LicenseNumber

,LicenseNumber as LicenseNumber_c

,Phonenumbercustomer,Calldate 

From yoursourceXyz.qvd

 

Partner:

Load

PartnerName

,PartnerName as PartnerName_p

,LicenseNumber

,LicenseNumber as LicenseNumber_p

From yoursource.qvd;

 

In Chart, use the new dimensions from respective tables

Dimension

correction

= aggr(

if(rank(count(LicenseNumber_c),4,2)<=10,PartnerName_p)

,PartnerName_p)

<---Uncheck show null values on this dimension    

Measures

=count(LicenseNumber_p)

=count(LicenseNumber_c)

 

 
Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunil-kumar5
Creator II
Creator II
Author

Dimension is giving an error- If takes 2-3 parameters.

I have put the Null() at the end but didn't work

vinieme12
Champion III
Champion III

sorry my bad

 

= aggr(

if(rank(count(LicenseNumber_c),4,2)<=10,PartnerName_p)

,PartnerName_p)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.