Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Joining tables in Qlikview

Hi,

I require a bit of help on joining tables in Qlikview.  Due to a lack of Indexes on the tables, it is impossible for me to create join on the tables in SQL without crashing the database server.  The only way I can pull out my information is to load the tables in that I require and then try and get Qlikview to join the tables, which I have done to some success.

However I now have an issue where my userdb_location table has a location field where I need to pull out the data CLO.  I need to pull out only the callref field from table Opencall_Table where location = CLO.  Please see attached the Table view.

Is this possible in Qlikview, if so how would I start going about this.

Regards,

Jon Ditchfield

export.png

1 Solution

Accepted Solutions
Highlighted
Not applicable

Jon,

if your test in on location (I did not catch it), it is certainly a RIGHT join (instead of left)

LEFT (keep what is in QlikView)

RIGHT (keep what you load: file or SQL)

Here, you want to keep in QV what will be loaded from SQL Add the where statement, it should work

Fabrice

View solution in original post

8 Replies
Highlighted

I give you the code to perform that operation:

MyTab:

noconcatenate

Load * from Opencall_Table ;

left join

load * from userdb_location;

MyNewTab:

Load * resident MyTab

Where location = CLO;

Drop table MyTab;

Hope it helps

Highlighted
Master
Master

Hi Jonathan,

I think best solution will be Applymap() function,where we can retreived data for only one field

Performance wise its good.

if you use Join it takes lot more time to retreive data as whole data is retreived

Highlighted
MVP
MVP

using applymap to filter


// load dimension

Userdb_location:

load .....................;

// create filter: table with 2 column, cust to join with Opencall_table and location to filter

Map: Mapping LOAD cust_id, location resident Userdb_location;

// load facts filtering by location of cust_id

Opencall_Table:

load

     *

where

  // applymap(mapping table name, field in input, value if not found) returns the second column

  ApplyMap('Map', cust_id, 'N/A') = 'CLO'

;

Highlighted
Not applicable

I have the following load script, however I cannot get it to work properly,  would someone be able to help me with this please?

MyTab:
LOAD callref,
     status,
     priority,
     owner,
     cust_id,
  firstname,
  surname,
  keysearch as cust_id,
  location;

SQL SELECT  callref,
      status,
      priority,
      owner,
      cust_id
FROM mydata.open;

SQL SELECT firstname,
   surname,
   keysearch,
   location
FROM mydata.user;

Regards,

Jon

Highlighted
Not applicable

Jon,

Can you try this:

MyTab:
LOAD callref,
     status,
     priority,
     owner,
     cust_id,
SQL SELECT  callref,
      status,
      priority,
      owner,
      cust_id
FROM mydata.open;

Left Join (MyTab)

LOAD

  firstname,

  surname,

  keysearch as cust_id,

  location;

SQL SELECT firstname,
   surname,
   keysearch,
   location
FROM mydata.user;

Fabrice

Highlighted
Not applicable

Hi Fabrice,

Thanks for the script, it seems to have joined the tables, however when I enter a where clause into either the SQL query or in the load section, it pulls through CLO in the location table, but it still shows all the data such as Callref.  If I select a call ref that is not location = 'CLO' it grays out CLO.  Basically I dont want any of the end users to be able to see anything other than call reference relating to CLO.

Can this be done at all?

Regards,

Jon Ditchfield

Highlighted
Not applicable

Jon,

if your test in on location (I did not catch it), it is certainly a RIGHT join (instead of left)

LEFT (keep what is in QlikView)

RIGHT (keep what you load: file or SQL)

Here, you want to keep in QV what will be loaded from SQL Add the where statement, it should work

Fabrice

View solution in original post

Highlighted
Not applicable

Thanks for the help Fabrice, and to everyone else who assisted aswell.