Working with two independent sources

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

Hi Qlik experts,

Working on two independent sources.

Source1:

LOAD

INLINE

[

LocationID,LocationLat,LocationLong

L1,51.5319519867171,0.00372337109717115

L2,51.5501908776835,-0.163974492584094

L3,51.4979607802573,-0.0643300900477039

];

Source2:

LOAD

INLINE

[

AdhocLocationID,AdhocLocationLat,AdhocLocationLong

K1,51.5319519867171,0.00372337109717115

K2,51.5502908776835,-0.163874492584094

K3,51.4654196112512,-0.286397466744351

];

Scenario is that both Source1 & Source2 are completely independent and are from two different sources.

But there is a requirement to form a consolidated data set with Source1 data as master and add new locations found in Source2 which are 100m and far from existing locations in Source1.

Distance between two LatLongs, formula is

"NUM((ACOS(SIN(Lat1*Pi()/180)*SIN(Lat2*Pi()/180)+COS(Lat1*Pi()/180)*COS(Lat2*Pi()/180)*COS((Long2*Pi()/180)-(Long1*Pi()/180)))*6371),'##.00')*1000" (in meters)

Expected result is as below : K1 doesnt add up because K1 and L1 are one and only same. K2 doesnt add up as distance between K2 and L2 is <100m.

LocationID,LocationLat,LocationLong

L1,51.5319519867171,0.00372337109717115

L2,51.5501908776835,-0.163974492584094

L3,51.4979607802573,-0.0643300900477039

K3,51.4654196112512,-0.286397466744351

I have achieved this through script by an outer join between Source1 and Source2 by adding a dummy column 'Dummy' in both the tables with same value across. So data set looks like this with outer join -

Dummy,LocationID,LocationLat,LocationLong,AdhocLocationID,AdhocLocationLat,AdhocLocationLong

Dummy,L1,51.5319519867171,0.00372337109717115,K1,51.5319519867171,0.00372337109717115

Dummy,L1,51.5319519867171,0.00372337109717115,K2,51.5502908776835,-0.163874492584094

Dummy,L1,51.5319519867171,0.00372337109717115,K3,51.4654196112512,-0.286397466744351

Dummy,L2,51.5501908776835,-0.163974492584094,K1,51.5319519867171,0.00372337109717115

Dummy,L2,51.5501908776835,-0.163974492584094,K2,51.5502908776835,-0.163874492584094

Dummy,L2,51.5501908776835,-0.163974492584094,K3,51.4654196112512,-0.286397466744351

Dummy,L3,51.4979607802573,-0.0643300900477039,K1,51.5319519867171,0.00372337109717115

Dummy,L3,51.4979607802573,-0.0643300900477039,K2,51.5502908776835,-0.163874492584094

Dummy,L3,51.4979607802573,-0.0643300900477039,K3,51.4654196112512,-0.286397466744351

So from the above data set, have caluculated distance for each LocationID vs AdhocLocationID record and have selected those AdhocLocationID's which doesnt have any <100m entry from above.

I believe there should be a better way of doing it in Qlikview either by script or at presentation layer.

This approach takes time with volume. Could you please advise.

Thanks in advance.

topic Working with two independent sources in QlikView

Working with two independent sources