Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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'
;
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
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
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
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
Thanks for the help Fabrice, and to everyone else who assisted aswell.