Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
For more information on the Exists() function, see: Exists ‒ QlikView
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?
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