Removing duplicates

[suraj_qlik]

Hello Guys,

Can you help to remove the duplicates from a chart which would look something like this:(sample data)

KPI_1	KPI_2	Correlation
A	B	.5
B	A	.5
A	D	.6
C	D	.5
D	A	0.6

 It should look like the following:

KPI_1	KPI_2	Correlation
A	B	.5
A	D	.6
C	D	.5

so since correlation of A and B is same as B & A, hence I want those to removed.

Please help.

[Anil_Babu_Samineni]

Does it mean, If KPI1 or KPI2 having similar values it should not point to duplicate value or Correlation?

[suraj_qlik]

Yes, correct @Anil_Babu_Samineni . We should be able to see just correlation of A & B and not B & A (as it will be same)

[Frank_Hartmann · Accepted solution]

tmp:
LOAD  In, RecId, Concat(Char) as Out,KPI_1,	KPI_2,	Correlation
GROUP BY In, RecId,KPI_1,	KPI_2,	Correlation;
LOAD *, Recno() as RecId,
  Mid(In,Iterno(),1) as Char
WHILE iterno() <= Len(In);
Load *, KPI_1&KPI_2&Correlation as In;
Load * Inline [
KPI_1,	KPI_2,	Correlation
A,	B,	0.5
B,	A,	0.5
A,	D,	0.6
C,	D,	0.5
D,	A,	0.6
];

NoConcatenate
final:
load *, if(Out=peek(Out,-1),Null(),Out) as test Resident tmp 
Where if(Out=peek(Out,-1),Null(),Out)<>Null() Order by Out; DROP Table tmp;