Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
sbiradar
Contributor
Contributor

Straight Table Chart Expression from two table data

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.

1 Reply
felipedl
Partner - Specialist III
Partner - Specialist III

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;