topic Exclude Records in QlikView

Exclude Records

kevbrown — Tue, 28 Feb 2017 11:29:17 GMT

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?

Re: Exclude Records

Peter_Cammaert — Tue, 28 Feb 2017 11:35:04 GMT

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

Re: Exclude Records

Peter_Cammaert — Tue, 28 Feb 2017 11:38:29 GMT

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

Re: Exclude Records

kevbrown — Tue, 28 Feb 2017 11:46:23 GMT

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?

Re: Exclude Records

Peter_Cammaert — Tue, 28 Feb 2017 11:51:22 GMT

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