Using Exists and Keep to limit the data load in Qlikview Script

Using Exists and Keep to limit the data load
Quite often, we need to restrict the amount of data that we are loading. We can usually do this
by comparing the value in a particular field to some other value (for example, Year > 2010),
but we might also need to load the data based on the values that have already been loaded
into memory.

Load the following script:
Sales:
LOAD * INLINE [
Year, Country, SalesPersonID, Sales
2011, Germany, 1, 1233
2012, Germany, 1, 2133
2013, Germany, 1, 3421
2011, UK, 2, 1567
2012, UK, 2, 2244
2013, UK, 2, 2567
2011, USA, 3, 1098
2012, USA, 3, 1123
2013, USA, 3, 1456
];

SalesPerson:
LOAD SPID As SalesPersonID,
SalesPersonName as SalesPerson;
Load * INLINE [
SPID, SalesPersonName
1, John Smith
2, Jayne Volta
3, Graham Brown
4, Anita Weisz
];
Budget:
LOAD * INLINE [
Year, Country, Budget
2012, Germany, 2100
2013, Germany, 3100
2014, Germany, 4100
2012, UK, 2100
2013, UK, 2600
2014, UK, 3100
2012, USA, 1100
2013, USA, 1200
2014, USA, 1300
];

These steps show you how to use Exists and Keep to limit the data load:
1. Add a listbox for Year and one for SalesPerson. Note that there are four values in
each. However, there is one extra sales person that we don't need at this time. There
is also a year's budget that we don't need to include. Modify the SalesPerson table
load as follows:
SalesPerson:
LOAD SPID As SalesPersonID,
SalesPersonName as SalesPerson
Where Exists(SalesPersonID, SPID);
Load * INLINE [
SPID, SalesPersonName
1, John Smith
2, Jayne Volta
3, Graham Brown
4, Anita Weisz
];
2. Modify the Budget load as follows:
Budget:
Left Keep (Sales)
LOAD * INLINE [
Year, Country, Budget
2012, Germany, 2100
2013, Germany, 3100
2014, Germany, 4100
2012, UK, 2100
2013, UK, 2600
2014, UK, 3100
2012, USA, 1100
2013, USA, 1200
2014, USA, 1300
];
3. Reload the script and note that each listbox only has three values. Open the table
viewer and note that the budget is still a separate table.
4. Modify the Keep statement for the Budget load to the following:
Inner Keep (Sales)
5. Reload and note that there are now only two values in the Year listbox.
How it works...
The Exists clause on the SalesPerson load checks to see if the value being loaded (SPID)
already exists in the already loaded data (SalesPersonID). If it does, then the sales person
gets loaded.
The Keep clause acts in a similar way to a Join statement, except that the tables are not
joined. Rows in the tables are only kept if they match the join. In the first case, we use a Left
Keep so the data in the Sales table is all retained and we just don't load the budget data
that doesn't match. In the second case, (the Inner Keep), both tables lose the records that
don't match an Inner.


 <!-- Begin BidVertiser code -->
<SCRIPT LANGUAGE="JavaScript1.1" SRC="http://bdv.bidvertiser.com/BidVertiser.dbm?pid=637728&bid=1590320" type="text/javascript"></SCRIPT>
<noscript><a href="http://www.bidvertiser.com/bdv/BidVertiser/bdv_publisher_toolbar_creator.dbm">toolbar creator</a></noscript>
<!-- End BidVertiser code -->