Compare values between one column(field) in straight table in UI

[Micki]

Hi folks, 

My table:

Column1	Column2
A	Adam
A	James
A	James
B	Mick
B	Mick
B	Mick
B	James
B	James
B	James
C	Adam
C	James
C	Mick
C	Jordan

And i want to compare values from Column1 = 'C' with rest Column1 <> 'C'

First data set from main table 

	Distinct
C	Adam
C	James
C	Mick
C	Jordan

and second dataset from main table which contains distinct values 

Distinct values

Adam

James

Mick

And expected result should be a measure in the same table which should show differences between those two datasets:

Difference

Jordan

Thank you in advance !

[MayilVahanan · Accepted solution]

Hi @Micki 

One of the method

Try like below, Create a variable like

vColumn2  =Concat( {<Column1-={'C'}>}DISTINCT Column2,',')

In Straight table,

Dim: Column1, Column2

Exp: If(Index(vColumn2, Column2)=0, Column2)

In text box,

=Concat(Distinct Aggr(If(Index(vColumn2, Column2)=0, Column2), Column1, Column2),',')

Hope it helps

[PriyankaShivhare]

Hi,

Data:
Load Distinct
Column1,
Column2,
if(Match(Column1,'A','B'),Column2)as ColumnAB,
if(Match(Column1,'C'),Column2)as ColumnC;
Load * Inline [
Column1, Column2
A, Adam
A, James
A, James
B, Mick
B, Mick
B, Mick
B, James
B, James
B, James
C, Adam
C, James
C, Mick
C, Jordan];

MapTest:
Mapping Load Distinct
ColumnAB as ColumnTest ,
'Match'
Resident Data ;

Test1:
Load
ColumnTest,
ApplyMap('MapTest',ColumnTest,'Not Match') as MatchField;
Load Distinct
ColumnC as ColumnTest
Resident Data ;

Drop Table Data;

Now Separate out the dataset with not match string in ColumnTest

Thanks,

Priyanka