topic Re: Find peers/neighbors amongst the data (missing values) in QlikView

Find peers/neighbors amongst the data (missing values)

Anonymous — Mon, 29 Dec 2014 13:56:01 GMT

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.

Re: Find peers/neighbors amongst the data (missing values)

preminqlik — Mon, 29 Dec 2014 20:49:33 GMT

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