Newbie question: referring to joined columns from an earlier load statement

[#1]

Dear fellow Qlikviewers,

I think my own background in SQL may be inhibiting the understanding of the QlikView JOIN statement, but I couldn't find a solution in the reference manual either. My problem is I think simply one of syntax. I want to join two tables and place some additional constraints based on the data in those tables:

For illustration, a simplified version of my problem. We might solve this case differently, but in my actual problem, I need the join for the construction of a composite key table.

Table1, columns: Code, Name1

Table2, columns: Code, Name2

Table3: join of the two tables, where the code matches, but the names do not.

I figured something like this would do the trick:

Table3:

LOAD *

RESIDENT Table1

WHERE Name1<>Name2; //This seems the natural place for the constraint

JOIN LOAD *

RESIDENT Table2;

However, this construction leads to the error that 'Name2' cannot be found. Apparently only the columns of the resident table are available in the where statement. I hope that makes the question in the topic of this post clear. Is there someone who can help me get this right?

Thanks in advance!

[jonathandienst · Accepted solution]

Hi

You are correct - do NOT think SQL when thinking of joins. The way I think of joins is that it extends the table by adding fields/columns from the source to the target - compared to concatenate which extends the table by adding records/rows from the source to the target**.

To do what you need, something like this:

//Initial load of table 1 into temp table

tmpTable:

LOAD * Resident Table1;

//Add Name2 field from table 2 (joining on Code)

Join(tmpTable)

LOAD * Resident Table2;

//Load final table using name compare condition

Table:

NoConcatenate

LOAD * Resident tmpTable

Where Name1 <> Name2;

//Not needed anymore

DROP Table tmpTable;

(The noconcatenate statement is required to prevent QV from auto concatenating the last load into tmpTable, as tmpTable and Table contain identical sets of fields)

Hope that helps

Jonathan

** this is a slight simplification, as joins with non-unique joining key(s) can increase the number of records

[#3]

A small addition: I had considered making a temporary table, but have implemented this incorrectly I guess:

Table4:

LOAD *

WHERE Name1 <> Name2; //Name2 is again not found...

LOAD * //this statement and the one below together give a table with Code, Name1, Name2, so why is Name2 not available in the upper load statement?

RESIDENT Table1;

JOIN LOAD *

RESIDENT Table2;

However, http://community.qlik.com/message/175559#175559 showed that if you create an additional load statement under the Table4 construction, you can place constraints on all the fields of Table4. I'd prefer not too start out with joining everything in a temporary table and then later reducing it, since I need to join multiple tables with several overlapping columns. Preventing a lot of entries from being created seems prudent, no?

Edit: apologies, had missed that someone had already suggested the temporary table approach in the meantime. Thanks for the suggestion! I will use it if I have to, but hope to see a cheaper solution. Is a solution like the Table4 in this post not feasible? It seems the Name2 column should be available to the highest load statement, right?

[jonathandienst]

Hi

The post you mention is correct, and the syntax of your script is valid. However, the join statement is only executed AFTER the preceding LOAD *;LOAD * Resident Table1 is compete. See the inline comments below...

Table4:

LOAD *

WHERE Name1 <> Name2;

//This first load is dependant on the subsequent load as there is no FROM/RESIDENT etc. The LOAD is considered

//as a single operation up until the first LOAD with a source FROM/RESIDENT.

LOAD * //this statement and the one below together give a table with Code, Name1, Name2, so why is Name2 not available in the upper load statement?

RESIDENT Table1;

//The initial load executes up to here only. So Name2 does not exist in the table before this line.

//Only the fields in Table1.

JOIN LOAD *

RESIDENT Table2;

So the JOIN is a completely separate LOAD statement and not part of the muti-level load above.

Hope that helps

Jonathan

[#5]

Thanks Jonathan for your contribution! Very helpful to get some insight into how the parsing works