JOIN and CONCATENATE, getting rid of empty values

[#1]

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?

[prieper · Accepted solution]

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

[#3]

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]

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]

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...

[#6]

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.

[#7]

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

[#8]

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.

[#9]

I had the similar requirment and works fine for me.

Try Inner Join

[#10]

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

-Zubair