Using data in loaded table to limit SQL WHERE

[#1]

Hello!

I have two tables in an SQL database.

One contains records, and looks a bit like this:

ID number	Description	Resolution	Assigned Agent
1	Forgotten password.	User given new password.	Hans
2	User complains about loud beeping noise.	User had two keyboards and was using one to put things on.	Gretel
4	User cant find printer.	Guided the user, printer was on his desk.	Tits McGee

The other one contains multiple rows of data for each record in the first table, like this:

ID Number	Action Type	Date
1	Record Opened	2014-01-01
1	Description Changed	2014-01-04
1	Record Closed	2014-01-11
3	Record Opened	2014-01-23
4	Record Opened	2014-02-09
5	Record Opened	2014-03-11

Now, what I want to do is import the entire first table, and then get the rows with the corresponding ID-numbers from the second table and store into a seperate table in quickview.

In other words, somthing like this:

Facts:

     LOAD

          [ID number];

SQL Select

     [ID number]

FROM

Table1;

Details:

     LOAD

          [Action Type];

SQL Select

     [Action Type]

FROM

     Table2

WHERE

     table2.[ID number] in Facts;

However, I get an error message complaining about incorrect syntax.

Is it possible to use a value from a previous LOAD in the next SQL WHERE statement? The WHERE-statement is in the SQL-query so I was thinking I should be using SQL-syntax and not Qlikview's funtions. Perhaps I'm doing it completely wrong? I need to keep the data in seperate tables once loaded into qlikview, no joins.

I'm guessing I could also just get the entire Table2 in the SQL Select and then limit my data in the LOAD with the match() function or something, but wouldn't that put a bigger strain on my database server? The real tables are millions of rows each.

Thanks in advance.

[Gabriel]

Hi,

QlikView support JOIN similar to SQL.

So try using RIGHT JOIN before 2nd table and you would have a final table with extra fields from the 1st table.

Hope this helps

[preminqlik]

make Left join

syntax :

table1:

Load *,[ID number] as     [ID Number],

from path;

left join (table1)

Load *

from table2:

store table1 into path\tablename.qvd;

[dmohanty]

Hi Niklas,

Check this attachment, if this is your need.

Regards!

[#5]

Unfortunately I can't join the tables, as the resulting table would contain multiple rows with the same ID number and description.

I need qlikview to have two resulting tables, one with all the records (table 1) and one with the record details for those records. As one of the columns is named "ID-number" in both tables, qlikview should do it's association automatically between these tables.

[#6]

Like I wrote in response to the comment above, I can't join these tables in qlikview and instead need to get two tables in qlikview with the seconds table being limited by the rows in the first table.

[#7]

Thank you.

But unfortunately I can't open the file as I am currently on the personal edition. Any chance you could post the load script you used?

[dmohanty]

Here it is as requested.

Regards!

[Gabriel · Accepted solution]

Hi,

In that case what you need is to use KEEP is another form of JOIN.

KEEP will ensure your final data model has those 2 tables.

Read Help file QV for KEEP

[martynlloyd]

The Where Exists clause is useful in many cases, but given the size of your tables, I recommend you simple join the two tables in the SQL statement for the second load, then you can use the SQL where. Or you could use a sub query

Where (ID Number) IN (Select [ID number] FROM Table1)

It looks like you understand SQL, but if you need help with that, let us know.

Best regards,

Marty.