how to only get results from tableB that are relevant to tableA?

[#1]

Hi all,

second question in one day, lots of time spent in QlikView today

Background:

I am using QlikView to query a remote database server.

By using the Connect > Select to select tables and columns,

then editing the Select statement to filter on relevant data.

By querying several tables in the same database, I can look up values.

Example:

the main table has "country_code" but no name for this.

I filter this part of the statement on country_code = '15' knowing that is Australia.

Then further down in my query I look this up against another table, something like

SQL Select "country_id", "country_name" WHERE "country_id" = '15';

// meaning I only want the name for country_code 15, not the entire list of country names in the world.

Question:

How can I do this when I cannot filter out results from the second table as easily?

tableA:

I run a query,asking to list me 1000 sales of this one product.

SQL Select "consumer_id", "product_id" WHERE product_id = '11111'

Limit 1000;

Then in another table I look up their consumer_ids against location.

something like

SQL Select "consumer_id", "location_id"

But I cannot specify "filter on consumer_id X and Y" as there are 1000 of them and I do not know these values even when writing the query.

Problem:

Not being able to filter on whom I want location for, I get every location_id in the world

Is there a way to pipe a value from one part of the query into another?

Or better yet, let the query run but on the Table/Graph object somehow "only show values that have a consumer_id" so all the other rows of Location_ids do not go on for thousands of rows of unrelevant data.

Hoping that made sense

thanks

Bjorn

[sujeetsingh]

Seems a complete model , you need to clarify with customer

[tresesco]

Not sure if I understood right. May be it would be a simple join with a basic filter like:

SQL Select Table1.Country, Table2.CountryID from Table1, Table2 Where Tabel1.CountryID=Tabel2.CountryID And Table1.Country='Australia' ;

[#4]

Tresesco,

That is the part that I cannot do, the filter on "Country= Australia"

Because I am first in TableA looking "who bought the product",

then in TableB:

"where are these consumers?"

By asking for Location_id based on Consumer_ID and I don't have the list of Consumer_ids until the first part of the query has run.

this is the query against TableB:

SQL Select "consumer_id", "location_id"

how to do something like "WHERE consumer_ids were the ones listed in the query against TableA earlier in the script"

thanks

BJorn

[SunilChauhan]

TableA:

load Keyfield,* from Table A;

TableB

load Keyfield,* from TableB where exists(keyfield,keyfield);

drop table A;

[CELAMBARASAN]

You could try Left Keep

TableA:

SQL Select "consumer_id", "product_id" WHERE product_id = '11111'

Limit 1000;

TableB:

Left Keep(TableA)

SQL Select "consumer_id", "location_id" FROM TableName;

[#7]

did not get a chance to try these today, will report back as soon as I do.

thank you again for taking the time to help.

Bjorn