Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with map and getting the latitude/longitude

Hey guys,

I am trying to update my database integrating with another different, and I am having now a problem when I try to see some maps that apply to certain places. it's like this:

I have a database called "Company_Name", each linked to another called "meta_key", inside there is "service center capability", "location latitude" and "location longitude". When I click on "service center capability", it shows the capabilities the specified service center have. I want then, for example, choose one capability, for example (Aircraft    BASE  Phenom 300), then it shows in the map all Companies that perform this maintenance BASE on Phenom 300s. The problem is that doesn't work doing that, because they don't like the latitude and longitude directed to it... Attached is the problem,

Can someone help me please???

Thank you alot.

1 Solution

Accepted Solutions
rbecher
MVP
MVP

Hi Julio,

this is the code for Generic Load:

GenTable:

Generic Load ID, meta_key, meta_value FROM wp_postmeta.qvd (qvd);

WP_postmeta:

LOAD Distinct ID FROM wp_postmeta.qvd (qvd);

FOR i = 0 to NoOfTables()

  TableList:

  LOAD TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'GenTable.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (WP_postmeta) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

NEXT i

LEFT JOIN (WP_postmeta)

LOAD ID, max(LATITUDE) as LATITUDE1, max(LONGITUDE) AS LONGITUDE1

FROM wp_postmeta.qvd (qvd)

GROUP BY ID;

DROP TABLE TableList;

Please see also attached QVW..

- Ralf

Update: There was a problem with the last JOIN. I fixed it..

Astrato.io Head of R&D

View solution in original post

10 Replies
Not applicable
Author

Anyone?

rbecher
MVP
MVP

Hi Julio,

I guess this has nothing to do with the map. It works if you select COMPANY_NAME which is the dimension of the map-chart. You should add a straight table with lat/long to see the result of your selections.

- Ralf

Astrato.io Head of R&D
Not applicable
Author

But Ralf, how I am supposed to do that directly in the script?

Not applicable
Author

Could you please upload something so I can see???

rbecher
MVP
MVP

Hi Julio,

this is the code for Generic Load:

GenTable:

Generic Load ID, meta_key, meta_value FROM wp_postmeta.qvd (qvd);

WP_postmeta:

LOAD Distinct ID FROM wp_postmeta.qvd (qvd);

FOR i = 0 to NoOfTables()

  TableList:

  LOAD TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'GenTable.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (WP_postmeta) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

NEXT i

LEFT JOIN (WP_postmeta)

LOAD ID, max(LATITUDE) as LATITUDE1, max(LONGITUDE) AS LONGITUDE1

FROM wp_postmeta.qvd (qvd)

GROUP BY ID;

DROP TABLE TableList;

Please see also attached QVW..

- Ralf

Update: There was a problem with the last JOIN. I fixed it..

Astrato.io Head of R&D
Not applicable
Author

Ralf, I don't even know how to say thank you for doing it! There's one more thing that I want to do, it's to use the substringcount formula to create a field (also linked with the latitude and longitude maps), that gets the 'servicecentercapability_manual', and filter what it is 'BASE Legacy 600', for example. I want for all aircrafts, but if do it for one, it would be already great.

Thank you alot since now Ralf!!!!!

rbecher
MVP
MVP

Hi Julio,

I added a Details table which holds all occurances of 'servicecentercertificationcapability_manual':

Details:

LOAD ID, subfield(servicecentercertificationcapability_manual, '||', IterNo()+1) as servicecentercertificationcapability

Resident WP_postmeta

While IterNo()<SubStringCount(servicecentercertificationcapability_manual, '||');

- Ralf

Astrato.io Head of R&D
Not applicable
Author

Ralf, this is just amazing, I am really grateful for this help,

I also managed to use it in another way, using substringcount directly in a multibox, wich also worked for me.

Just one more question, if I wanted to choose in a general way, for example "Phenom 100", because inside the servicecentercertificationcapability_manual I have 'BASE Phenom 100', and 'LINE Phenom 100', how would I do that directly in the script?

Once again, this is just amazing, it's like, another level of programming in Qlikview, thank you alot for you help

rbecher
MVP
MVP

Maybe just replace(replace(Field, 'BASE ', ''), 'LINE ', '')

Btw. you can search in a listbox for 'Phenom 100' as well..

Astrato.io Head of R&D