topic Referencing columns from another table in a join in QlikView

Referencing columns from another table in a join

Thu, 18 Nov 2010 21:32:18 GMT

In my qlikview script I can't figure out how to reference a column from Table1 in the load for Table2 . Table1 is from a file and Table2 is in a database:

Table1:

Name ID Job

Alex 1 Technology

Sam 2 Bank

Jeff 3 Engineer

Table 2

ID JobDetail

1 ApplicationDevelopemnt

3 Structural

TableMain:

Load Name, ID, Job from file1.csv

Left Join(TableMain)

Load

ID,

If(isnull(JobDetail),Job,JobDetail) as JobDetail;

From SQL Select * from Table2;

This gives me an error saying Field not Found <Job>. I am guessing because job is from table1 and i am trying to use it in a load for table2. I am guessing I can load everything first into a temp table then reprocess that but that seems inefficient.

Thanks,

Alex

Referencing columns from another table in a join

pover — Thu, 18 Nov 2010 21:43:51 GMT

Usually you join the tables first and then use the resident command to call the joined table again and do logic that needs columns from both tables.

But in your case you one of the tables has just 2 columns so you can do a mapping and save time. Look at the example below.

Table2:
Mapping Load ID,
JobDetail;
From SQL Select * from Table2;

TableMain:
Load Name,
ID,
Job,
If(isnull(applymap('Table2',ID,null())),Job,applymap('Table2',ID,null())) as Job Detail
from file1.csv

Regards.

Referencing columns from another table in a join

Thu, 18 Nov 2010 21:56:16 GMT

Ahh excellent. Thank you!