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.
