Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using data in loaded table to limit SQL WHERE

Hello!

I have two tables in an SQL database.

One contains records, and looks a bit like this:

ID numberDescriptionResolutionAssigned Agent
1Forgotten password.User given new password.Hans
2User complains about loud beeping noise.User had two keyboards and was using one to put things on.Gretel
4User 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 NumberAction TypeDate
1Record Opened2014-01-01
1Description Changed2014-01-04
1Record Closed2014-01-11
3Record Opened2014-01-23
4Record Opened2014-02-09
5Record Opened2014-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.

1 Solution

Accepted Solutions
Gabriel
Partner - Specialist III
Partner - Specialist III

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

View solution in original post

12 Replies
Gabriel
Partner - Specialist III
Partner - Specialist III

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
Specialist II
Specialist II

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
Partner - Specialist
Partner - Specialist

Hi Niklas,

Check this attachment, if this is your need.

Regards!

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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
Partner - Specialist
Partner - Specialist

Here it is as requested.

Regards!

Gabriel
Partner - Specialist III
Partner - Specialist III

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
Partner - Creator III
Partner - Creator III

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.