Find peers/neighbors amongst the data (missing values)

[Anonymous]

I have the following data set:

column1, value
ABCD, data1
ABCD, data2

ABCD, data3

ABCD, data4

EFGH, data1

EFGH, data2

EFGH, data3

EFGH, data4

EFGH, data5

... and so on.

Q. By load script or expression how can I return the missing data values between column1 data points. I.e. How dis-similar ABCD is to EFGH in regard to their data values?

E.g. for the above, ABCD and EFGH have 1 value (data5) NOT in common.

RETURN:
column1,  peer, count_values_not_in_common, value_not_in_common

ABCD,  EFGH,  1, data5

.... and so on.

Thank you in advance. Happy New Year.

P.s. this analysis is linked to Find peers/neighbors amongst the data which has been answered already, but provides instead the count of common values between column1 data points.

[preminqlik · Accepted solution]

hi there,hope this helps you, find attachment

t1:

LOAD * INLINE [

    column1, value

    ABCD, data1

    ABCD, data2

    ABCD, data3

    ABCD, data4

    EFGH, data1

    EFGH, data2

    EFGH, data3

    EFGH, data4

    EFGH, data5

    HIJKL, data1

    HIJKL, data2

    HIJKL, data3

    HIJKL, data4

    HIJKL, data6

];

t2:

Load *

Where countvalue=1;

Load value as VALUE_NEW,

count(value) as countvalue

Resident t1 Group by value;

NoConcatenate

t3:

Load column1 as peer,

value as value_not_in_common

Resident t1 where Exists(VALUE_NEW,value);

join(t1)

Load peer,

value_not_in_common,

count(value_not_in_common) as count_values_not_in_common

Resident t3 group by peer,value_not_in_common;

DROP Table t3;

NoConcatenate

Load *

Resident t1 where column1<>peer;

DROP table t1;

/*

-Many Thanks

Prem kumar Thangallapally