QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
New Contributor II

Find peers/neighbors amongst the data (missing values)

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.

Tags (3)
1 Solution

Accepted Solutions
Valued Contributor II

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

hi there,hope this helps you, find attachment

t1:

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:

Where countvalue=1;

count(value) as countvalue

Resident t1 Group by value;

NoConcatenate

t3:

value as value_not_in_common

Resident t1 where Exists(VALUE_NEW,value);

join(t1)

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

Resident t1 where column1<>peer;

DROP table t1;

/*

-Many Thanks

Prem kumar Thangallapally

Valued Contributor II

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

hi there,hope this helps you, find attachment

t1:

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:

Where countvalue=1;

count(value) as countvalue

Resident t1 Group by value;

NoConcatenate

t3:

value as value_not_in_common

Resident t1 where Exists(VALUE_NEW,value);

join(t1)

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