Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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