Straight Table Chart Expression from two table data

sbiradar — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

I have two tables both are not related to each other , both tables have geo coordinate data (Easting & Northing). I want to find distance between Source point(each record from TABLE1) to all destination points(each record from TABLE2).

I am trying to achieve this via Straight table chart in which am using expression column to populate the distance. I want to display the chart only when User selects ID from TABLE1 in selection box and populate the records only for ID selected and the distance calculated from selected ID to all rows in TABLE2.

Sample data :

TABLE1:

ID Easting Northing

1 683599 365839

2 834570 238409

TABLE2

ID Easting Northing

1 683599 365839

2 834570 238409

3 683599 365839

4 834570 238409

5 683599 365839

6 834570 238409

7 683599 365839

8 834570 238409

I have tried my expression which calculates the distance in Chart but data is not getting populated. Can anyone help here.

Re: Straight Table Chart Expression from two table data

felipedl — Mon, 30 Jul 2018 18:40:45 GMT

I'd say you'd have to do the combination in script side, something like:

// Simple euclidian distance for two points
set distance = sqrt(pow($1-$3,2)+pow($2-$4,2));

TABLE1:
load * Inline
[
ID,Easting, Northing
1,68.3599,36.5839
2,83.4570,23.8409
];

// Joining all the values of table 2 to table 1 for distance calculation for all possibilities
join (TABLE1)
Load Easting2, Northing2 Inline
[
ID,Easting2, Northing2
1,68.3591,36.5834
2,85.4570,23.8412
3,62.3599,36.5839
4,87.4570,23.8409
5,58.3599,36.5839
6,33.4570,23.8409
7,48.3599,36.5839
8,13.4570,23.8409
];

// creates the distance, using the distance function defined earlier.
_tmp:
Load
*,
$(distance(Easting,Northing,Easting2,Northing2)) as Distance
Resident TABLE1;


drop table TABLE1;

topic Straight Table Chart Expression from two table data in QlikView

Straight Table Chart Expression from two table data

Re: Straight Table Chart Expression from two table data