Build Table from Existing Data

[rajan]

Table 1:

_id	geolocation.type	cityId	stateId	countryId	locationId
1	POINT	abc	xyz	pqr	abc1
2	POINT	abc	xyz	pqr	abc1
3	POINT	abc	xyz	pqr	abc2
4	POINT	abc	xyz	pqr	abc2

Table 2:

_id	geolocation.idx	geolocation.coordinates
1	0	-75.2
1	1	45.3
2	0	-75.234
2	1	45.231
3	0	-75.123
3	1	45.23
4	0	-75.455
4	1	45.11

MongoDB schema is: {_id, geolocation: {type:POINT, coordinates:[lng, lat] }, cityId, stateId, countryId, locationId}.

On loading the data into Qlik from MongoDB, Qlik loads the data into Table1 and Table2.

I would like to plot the geo-coordinates on a map but I don't know how to do it with tables on such a structure. How to plot the _ids on the map with the geo-coordinates given in Table2.

Thank You.

[QFabian · Accepted solution]

Hi @rajan , i built this and it works, please check :

A:
LOAD * INLINE [
_id, geolocation.type, cityId, stateId, countryId , locationId
1, POINT, abc, xyz, pqr, abc1
2, POINT, abc, xyz, pqr, abc1
3, POINT, abc, xyz, pqr, abc2
4, POINT, abc, xyz, pqr, abc2
];

B:
LOAD * INLINE [
_id, geolocation.idx, geolocation.coordinates
1, 0, -75.2
1, 1, 45.3
2, 0, -75.234
2, 1, 45.231
3, 0, -75.123
3, 1, 45.23
4, 0, -75.455
4, 1, 45.11
];

C:
LOad
_id,
geolocation.coordinates as Lon
Resident B
Where
geolocation.idx = 0;

left join
LOad
_id,
geolocation.coordinates as Lat
Resident B
Where
geolocation.idx = 1;

Geo:
Load
[_id],
GeoMakePoint(Lat,Lon) as Point
Resident C;
drop table C;

Then just pick Point field to draw the map

[rajan]

Hi @QFabian , Thank You for this. I will definitely try it out. 

Just an addition query, the data in MongoDB is updated every 15 seconds and qlik is connected to MongoDB, so will the map stay up-to-date in such a scenario if I use your solution? If not, can you help me with it?

Thank You.

[QFabian]

Yes, the limitation is the time that qlik needs to relod each time.

[rajan]

Does qlik reload on its own or is there a script or a setting that needs to be tweaked to make qlik reload after certain time period and stay up-to-date with MongoDB data?

Thank You

[QFabian]

Normally, reload tasks with desired frecuency are scheduled in QMC, Qlik Management Console

[rajan]

Thank You for helping me out.