topic Retrieve a value from a resident table in QlikView

Retrieve a value from a resident table

Fri, 06 Aug 2010 16:17:39 GMT

HI,

I'm sorry to ask this because I feel this is a piece of cake. But I searched for quite some time and cannot make out how to achieve this.

I got a table with ZIP codes and GPS coords:


LOAD ZIP as GEO_ZIP,
 Latitude as GEO_Latitude,
 Longitude as GEO_Longitude
FROM Data\GEO\ZIP.xlsx
(ooxml, embedded labels);

Now if want to show data in another table filtered by the GPS coords. So I have an input box to enter a ZIP code.

Now I need to lookup the relevant GPS coords and use them to filter the table.

How can I get the Longitude/Latitude into variables or do this in any other way? Do I need to write a macro? I tried a triggered "Field Selection" with field: GEO_ZIP and Search Item: $(myZIP) and myZIP, but that simply kills the selection for GEO_ZIP.

I must add that I will need to repeat this for up to 50 ZIP codes in parallel.

Thanks for any kind of support.

Best regards,

Georg

Retrieve a value from a resident table

Sun, 08 Aug 2010 09:09:59 GMT

Please let mt know if I need to clarify the setup or my question.

In short it is: How to retrieve values from a reident table looking up with a value from a variable and store the looked up values into other variables.

Best regards,
Georg

Retrieve a value from a resident table

Sun, 08 Aug 2010 18:42:46 GMT

Instead of using an input box for the cirteria, have you tried using a search object?

Retrieve a value from a resident table

Mon, 09 Aug 2010 08:26:36 GMT

Yes, but I don't see how that would ever get me the result into a variable..?

Selecting the ZIP in my case is a step I need to do to have a second table filtered with the results of the lookup in the first table.

input box: vMy_Zip

table "ZIP_coords"

ZIP Lon Lat
22408 51,3434 26,23322
22412 52,34343 25,11111
...

table "locations"

51,3434 26,23322 LocationA
...

Entering 22408 in the input box shall retrieve or just show "LocationA" in table "locations".

Best,
Georg

Retrieve a value from a resident table

Mon, 09 Aug 2010 11:48:58 GMT

Hi,

If i understood it you can create two variables like that.

After selecting the GEO_ZIP it automatically updates the variables.

$GeoLat = max(GEO_Latitude)

$GeoLong = max(GEO_Max)

After that you can use these variables in set analysis and filter your data.

Retrieve a value from a resident table

Mon, 09 Aug 2010 13:40:08 GMT

As I wrote I will have to do this for 50 locations. And if I "select" it I will have to make 50 "copies" of that table. What's more I do not understand how or where to use your statement. I think it is not a macro. So I wonder where to put it? If you could offer me some more explanation I'd be gratefuil.

I rather thought it would be possible to have a macro rtiggered that will look up the values in the table and write it to the variables. I'm very familiar with VB -- but not with the QV object model. So any advise is appreciated.

Best,
Georg