Concatenate where exists

[#1]

Hi,

I'm quite new to QlikView and dealing with a concatenate problem.
I have 2 excel files I'd like to concatenate. File1Account:
AccountID, Accountname, Date, Sales,...

File2Account:
AccountID, Accountname, Date, Order,....

I'd like to concatenate only the accounts from file 2 where the account_id exists in both files.

How would it manage it if I only want to load the data from File2 where Account exists in file1.

Thanks for your help.

[eric_dielessen]

Try to use Where Not Exists('AccountID', AccountID2) when loading the second file. It will then only load records with an AccountId that already exist in your qlikview cloud. It's important to use two different names, otherwise it will load only one record per AccountId from your second file.

Example:

File1:
Load AccountId, AccountName, Date, Sales;
SQL Select AccountId, AccountName, Date, Sales From File1Account;

Concatenate(File1)
Load AccountId2 As AccountId, AccountName, Date, Order
Where Exists('AccountId', AccountId2);
SQL Select AccountId As AccountId2, AccountName, Date, Order From File2Account;

[#3]

Hi Eric,

thanks for your help:
I don't understand " It's important to use two different names, otherwise it will load only one record per AccountId from your second file."

I tried without two different names in a test file and it seems to work (see attached file).
Did I get something wrong?

Testdata looks like this:
File1:
AccountID Text1
===================
1 test1
2 test2
3 test3
4 test4
5 test5

File2:
AccountID Text2
=================
1 test1
2 test2
3 test3
4 test41
4 test42
5 test51
5 test52
5 test51
6 test6
7 test7

Can I manage it with concatenate to get only the data that exists in both files? (e.g) AccountID=1 isnt't in File1
Or do I have to manage this with an inner join?
Thanks

[eric_dielessen]

You're right about the different names; using different names is only necessary when you want to load records with an AccountId that do NOT exist already in the first file.

In other words, the concatenate can be used as in your example.

INNER Join will only work when

• There is only one record per AccountId in the first table AND
• There are no measures (fields that will be used in for example sum-expressions ) in the first file

[#5]

Ah thanks now I get it.

is it also possible to use Where exists for cleaning up data?

eg.

Table Employees:
emp_id, emp_name,...

Table Contracts:
contract_id, emp_id

in case I have persons in my table employees that don't exists in table contract and I want to remove them during load (so employees and contracts is consistant).

[eric_dielessen]

Yep ... that's the way how to load only those records that have a relation to a record in an earlier loaded table.