Nearest neighbor search calculation in Qlik Table Chart
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.
