Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to create a logic that i have worked out in SQL,
but i don't know how to craete it in qlikview script.
I have a table that describes ads, on a dialy level,
which says if the ad had an Upgrade ad (there are many types of upgrades, like: boost, premiumm, etc.),
or a base ad,
my goal is to take only premium ads WHICH HAD IN THE SAME DAY a basic ad
Example:
table name: Fact
Columns: id, upgarde, date
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)
*note: there a 10 types of premium, so i may see situations like:
column: id, upgrade, date
values: 123, basic, 1.1.2015
123, premium, 1.1.2015
123, boost, 1.1.2015
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
// to define an order of precedence
// JOIN in the details at the end
DROP Table TempFact;
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.
I have, but I don't want to over load the SQL server,
isn't it most optimized when i pull it to a qvd, and work on it?
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.
excellent,
do u have an idea how to do it in Qlikview?
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.
great,
correction, i will need to comppose the key only from 2 columns: date, id
Basic:
LOAD id, upgrade, date,
id & '|' & '|' & Num(date) AS [%MatchKey],
FROM qvd;
LEFT JOIN
Upgrades:
LOAD *
id & '|' & '|' & Num(date) AS [%MatchKey],
FROM qvd
WHERE upgrade Match ('premium','boost')
AND Exists (key, key)
how should i write the last row?
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
// to define an order of precedence
// JOIN in the details at the end
DROP Table TempFact;
thanks for your proffesional answer!
can you please clariy this statement?
"
TempFact:
SQL SELECT * FROM Fact; // Completely relieve the DB. QV can handle many rows
"
l logic Why do u put SQL before it? what is the meaning?
i plan to load it from a qvd, so isn't it better to load from a qvd?