Exclude Records

[kevbrown]

Hi,

I am bringing in 2 tables to QlikView and concatenating them, However some of the records in the second table are already in the first table and I need to exclude them

So I have a "Revenue" table with Name, Date, Product and "Transaction Number" with values 1, 2, 3, 4, 5, 6

and a second table "Revenue 2" with the same fields with values Transaction Numbers of 2, 5, 7, 8

I don't want Transactions 2 and 5 from "Revenue 2" table to concatenate to the "Revenue" table

Easy fix?

[Peter_Cammaert · Accepted solution]

Easy to do in QV. I'm using dummy code, the magic is in the Exists() function call:

// Load everything from Revenue1 source

[Revenue]:

LOAD * FROM [DataSourcePath1] (options); // Includes a field called TransactionID

// Add Revenue2 - omit existing Transactions by checking field TransactionID

CONCATENATE (Revenue)

LOAD * FROM [DataSourcePath2] (options)

WHERE Not Exists(TransactionID); // Won't load existing TransactionID rows

Best,

Peter

[Peter_Cammaert]

For more information on the Exists() function, see: Exists ‒ QlikView

[kevbrown]

Thanks that worked perfectly.  On the same lines, what if I had a list in excel of the Transaction Numbers that I would like to exclude. from the second table so they don't feed into the first table?

[Peter_Cammaert]

Best (and quickest) way to handle variable exclusion lists is to load your Excel list in a MAPPING table before loading the second part of the available transactions. The Exists() call gets replaced by a call to applymap() that returns a selected value (by you) for every record that needs to be excluded. Add some glue logic and you're set.

Info on Mapping and the use of applymap(), see here: Mapping ‒ QlikView