Not able to compare the two columns

[lokeshmanian]

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

[Anil_Babu_Samineni]

@lokeshmanian Do you have any values that is matching in your table? If so, then the condition will fulfill. 

[lokeshmanian]

there is match if i use IF condition to create a flag, example match means 1, not 0....

[Or]

Sum(IF([Segment] <> A1_Segment,'Yes',0))

What is sum('Yes'), exactly? Did you perhaps mean to place a 1 there?

[lokeshmanian]

Yes, your correct, SUM('Yes')... initially i tried with Sum(IF([Segment] <> A1_Segment,1,0))--It is not working..

[qv_testing]

you can compare with SubStringCount(FieldName1, FieldName2) function.

Please provide example with sample data and expected output and you will get quick response.

[Chanty4u]

Try this 

Count({<Segment -= A1_Segment>} DISTINCT Common_number)

[lokeshmanian]

Please check this example data :

[lokeshmanian]

It is not working..

[vinieme12]

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