Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have issue ,I have two tables and want to count the number of unique customer number which has segment variation with two columns.. i have connected through customer number as key and count the fields which is not equal in both segment column,, example..
Table1:
Customer Number as Common_number,
Segment,
Confidence %,
Revenue
FROM [CustomerList.qvd];
Table2:
Customer Number as Common_number,
A1_Segment,
A1_Confidence %,
A1_Revenue
FROM [CustomerCatList.qvd];
I tried with below formula is not working:
1)Sum(IF([Segment] <> A1_Segment,'Yes',0))
2)Sum(IF([Segment] <> A1_Segment,Distinct Common_number ,0))-->(used -=)
Some issue in data set:
Table1:-- More data , one Customer Number has many segment 3-4
Table2:-- Less data, one Customer Number has one segment
Expected Result :
How many unique customer number has segment variation between these two fields.
Please help me on this...
@lokeshmanian Do you have any values that is matching in your table? If so, then the condition will fulfill.
there is match if i use IF condition to create a flag, example match means 1, not 0....
Sum(IF([Segment] <> A1_Segment,'Yes',0))
What is sum('Yes'), exactly? Did you perhaps mean to place a 1 there?
Yes, your correct, SUM('Yes')... initially i tried with Sum(IF([Segment] <> A1_Segment,1,0))--It is not working..
you can compare with SubStringCount(FieldName1, FieldName2) function.
Please provide example with sample data and expected output and you will get quick response.
Try this
Count({<Segment -= A1_Segment>} DISTINCT Common_number)
Please check this example data :
It is not working..
You need to Join Table1 and Table2 since the relation is not 1-1 you will not be able to compare it as
[Segment] <> A1_Segment ; this needs to be a row level operation
Alternatively since Table2 has unique values; you can check if Table1 has more unique combinations than Table1 as below
= Count(Distinct Aggr( if(Count(Distinct A1_Segment)<>Count(Distinct Segment),CustomerNumber) , CustomerNumber) )