Joining tables in Qlikview

[#1]

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

[alexandros17]

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

[Anonymous]

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

[maxgro]

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'

;

[#5]

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

[#6]

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

[#7]

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

[#8 · Accepted solution]

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

[#9]

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