Nearest neighbor search calculation in Qlik Table Chart

Pranay_J — Fri, 10 Jun 2022 06:28:23 GMT

Hi I am trying to find nearest neighbor for an entity from a list of similar entities by considering multiple dimensions. In doing so I am stuck at a situation as below:

Entity	Value	Calculated field
E1	0.1
E2	0.4
E3	0.4
E4	0.2
E5	0.9
E6	0.0
E7	0.9
E8	0.0
E9	1.0
E10	0.9
E11	0.7
E12	0.1
E13	0.3
E14	0.6

In the calculated field I should be able to perform an operation like min((E1value -E2value),(E1value-E3value),(E1value-E4value....)). This is an example for first cell, in the second cell the calculation should be min((E2value -E1value),(E2value-E3value),(E2value-E4value....)) and so on for other cells.

My actual problem looks something like this:

Entity	Dim1	Dim2	Dim3	Required Entity
E1	0.1	0.2	1.0
E2	0.4	0.9	0.9
E3	0.4	0.6	0.4
E4	0.2	0.1	0.7
E5	0.9	0.5	0.8
E6	0.0	0.8	0.7
E7	0.9	0.8	0.3
E8	0.0	0.4	0.6
E9	1.0	0.2	0.1
E10	0.9	0.6	0.1
E11	0.7	0.3	0.2
E12	0.1	0.1	0.9
E13	0.3	1.0	0.8
E14	0.6	0.6	0.5

Required entity for every row is chosen from within the above table with min(sqrt((Dim1 of E1 - Dim1 of E2)**2+(Dim2 of E1 - Dim2 of E2)**2+(Dim3of E1 - Dim3 of E2)**2), sqrt((Dim1 of E1 - Dim1 of E3)**2+(Dim2 of E1 - Dim2 of E3)**2+(Dim3of E1 - Dim3 of E3)**2) .... till sqrt((Dim1 of E1 - Dim1 of E14)**2+(Dim2 of E1 - Dim2 of E14)**2+(Dim3of E1 - Dim3 of E14)**2)

Please note: I cannot do this calculation prior in script editor as these dimensions and the scaling of the metrics vary as per user selections and computing for all the combinations prior is not possible.

Any help would be greatly appreciated.

Thanks,

Pranay

topic Nearest neighbor search calculation in Qlik Table Chart in App Development

Nearest neighbor search calculation in Qlik Table Chart