topic Concatenate where exists in QlikView

Concatenate where exists

Fri, 21 Aug 2009 16:16:02 GMT

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.

Concatenate where exists

eric_dielessen — Fri, 21 Aug 2009 17:04:39 GMT

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;

Concatenate where exists

Fri, 21 Aug 2009 18:24:38 GMT

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

Concatenate where exists

eric_dielessen — Fri, 21 Aug 2009 18:38:51 GMT

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

Concatenate where exists

Fri, 21 Aug 2009 18:59:55 GMT

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

Concatenate where exists

eric_dielessen — Fri, 21 Aug 2009 19:11:14 GMT

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