Customer Base

[sunil-kumar5]

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

[GeorgePhilips23]

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]

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]

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)

[vinieme12]

= 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)

[sunil-kumar5]

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

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

[vinieme12 · Accepted solution]

sorry my bad

= aggr(

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

,PartnerName_p)