Since your original logic ignores upgrades that happen at a later date than the initial basic entry (and that fail the conditions because of not having a basic entry on the same date), you could reverse the steps:
TempFact:
SQL SELECT * FROM Fact; // Completely relieve the DB. QV can handle many rows
Fact:
LOAD id, date, upgrade AS originalupgrade RESIDENT TempFact
WHERE upgrade = 'basic';
INNER JOIN (Fact)
LOAD id, date, upgrade AS newupgrade RESIDENT TempFact
WHERE (upgrade = 'premium') OR (upgrade = 'boost') OR ...; // Add the other eight
// or create a mapping table
// To drop the doubles (a boost and premium on the same day) you'll have
Be aware that you can write your full SQL syntax that is supported by your SQL database for everything that you write in a SQL statement between the keyword SQL and the following semicolon in your Load Script. Like this:
SQL // all lines until the semicolon are sent unmodifed to your SQL DB:
SELECT *
FROM Fact AS a
WHERE (a.upgrade='premium' OR a.upgrade='boost')
EXISTS (SELECT *
FROM Fact AS b
WHERE upgrade='basic' AND a.id=b.id AND a.date=b.date)
;
However you might not have a SQL database at all - please let us know and then we can advice on how to do the same logic in a Load Script without a SQL datasource.
The SQL Exists does not exist in QlikView - you do have and Exists inter-record function which word wise might lead you into thinking it is very similar... But it works on only values that at the point of execution are present in a specific field in memory. Exists in SQL works on the entire set of records in a result query and will return entire rows not a single column.
Yes that is one main reasons for using QVD. Then you keep a history in QVD's and consolidate by using pure Load Script functionality. Unless you install a free to use "express" type of SQL database for staging purposes. Then you can still use your SQL skills to get a solution.
In a QVD-scenario you will have to make the QlikView Exists work and it depends on a single field match. To achieve this you will have to have a new field which is a concatenation of the three key fields in your SQL.
Upgrade, ID and Date.
LOAD SCRIPT suggestions:
Fact: // The population of your intial Fact
LOAD
id & '|' & upgrade & '|' & Num(date) AS [%MatchKey],
*;
SQL ....... ;
and then you will use this new [%MatchKey] field to mach history facts to newly arrived facts.
Since your original logic ignores upgrades that happen at a later date than the initial basic entry (and that fail the conditions because of not having a basic entry on the same date), you could reverse the steps:
TempFact:
SQL SELECT * FROM Fact; // Completely relieve the DB. QV can handle many rows
Fact:
LOAD id, date, upgrade AS originalupgrade RESIDENT TempFact
WHERE upgrade = 'basic';
INNER JOIN (Fact)
LOAD id, date, upgrade AS newupgrade RESIDENT TempFact
WHERE (upgrade = 'premium') OR (upgrade = 'boost') OR ...; // Add the other eight
// or create a mapping table
// To drop the doubles (a boost and premium on the same day) you'll have
