Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

JOIN and CONCATENATE, getting rid of empty values

Hi,

I have two tables, T1: A, B, C, D, E and T2: A, F

When I use LOAD normally and add a F table I get all the values of F, what I want to do is to only include the F-values which A value is present in T1. So F-values which A-values isn't included in T1 should be excluded.

I guess this is a INNER JOIN but I can't get it to work.

How do I rewrite my load-statement to only include my desired values?

Or is there a way in the listbox properties to write an expression to exclude certain values?

1 Solution

Accepted Solutions
prieper
Master II
Master II

LEFT JOIN should be your friend:

T1: LOAD A, B, C, D, E FROM .....;
T2: LEFT JOIN (T1) LOAD A, F FROM ....;


"T2" is not necessary in the script.

HTH
Peter

View solution in original post

9 Replies
prieper
Master II
Master II

LEFT JOIN should be your friend:

T1: LOAD A, B, C, D, E FROM .....;
T2: LEFT JOIN (T1) LOAD A, F FROM ....;


"T2" is not necessary in the script.

HTH
Peter

Not applicable
Author

Now I have:

Orders:
LOAD OrderID,
ProductID,
Product,
CustomerID,
EmployeeID
FROM
Sources\Sales.xls
(biff, embedded labels, table is Orders$);

Directory;
Employees:
LEFT JOIN (Orders) LOAD EmployeeID,
EmployeeName
FROM
Sources\Employee.xls
(biff, embedded labels, table is Employees$);

But still I dont get the desired results. What I want is for the Employees that aren't included in the Orders table to be excluded (Employees without orders aren't listed)

prieper
Master II
Master II

Please add a WHERE-clause with the EXIST-function:

LEFT JOIN LOAD ... FROM ... WHERE EXISTS('EmployeeID');


HTH
Peter

edit: Lars is perfectly correct - the above does not make any sense
Peter

larsc
Partner - Contributor III
Partner - Contributor III

No, the "where exists" won't help. You already have left join, so you will only join EmployeeName with those EmployeeID's that already exist in the Orders table. What exactly isn't working? Don't you get any EmployeeNames at all in the resulting table after running the script? If so, I suspect there isn't any match between the EmployeeID's in the Order table and the EmployeeID's in the Employees table.

Verify your data. If it still look OK, try use "trim(text(EmployeeID)) as EmployeeID" on both tables to ensure correct formatting of the EmployeeID. QlikView is a little sensitive if there are blank spaces and so...

Not applicable
Author

What happens (regardless if I use Left join or not) is that the result still isn't shrunk down, it still adds all of the employees and their names, even though they don't have an order connected to them.

I've never come across a scenario where there's no employees at all, it's the other way around, the employees are always there. The matching between Order and Employee table works fine.

Not applicable
Author

Would help a lot for us to answer your query if you could upload sample data for the problem

Not applicable
Author

Im sorry but I don't know how to include example data.

But I'm sure that the data is correct and that the connection between Orders and Employees work since I can see the connection when I select an EmployeeName but the problem is that I get all of the "not working" employeenames as well, which is very annoying since it's only 25% of the employees that has an order connected to them. So in most cases when I choose EmployeeName it results in no match on the Order side.

Not applicable
Author

I had the similar requirment and works fine for me.

Try Inner Join

Not applicable
Author

After the inner joing the relationship is not visible in Table Viewer? Is it a normal functionality or I am missing something here ?

-Zubair