SQL exists in Qlikview

[Anonymous]

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

[petter]

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.

[Anonymous]

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?

[petter]

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.

[petter]

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.

[Anonymous]

excellent,

do u have an idea how to do it in Qlikview?

[petter]

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.

[Anonymous]

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?

[Peter_Cammaert · Accepted solution]

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;

[Anonymous]

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?