topic Re: SQL exists in Qlikview in QlikView

SQL exists in Qlikview

Anonymous — Sun, 11 Jan 2015 11:14:52 GMT

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

Re: SQL exists in Qlikview

petter — Sun, 11 Jan 2015 11:35:44 GMT

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.

Re: SQL exists in Qlikview

Anonymous — Sun, 11 Jan 2015 11:39:37 GMT

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?

Re: SQL exists in Qlikview

petter — Sun, 11 Jan 2015 11:40:11 GMT

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.

Re: SQL exists in Qlikview

petter — Sun, 11 Jan 2015 11:42:37 GMT

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.

Re: SQL exists in Qlikview

Anonymous — Sun, 11 Jan 2015 11:46:21 GMT

excellent,

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

Re: SQL exists in Qlikview

petter — Sun, 11 Jan 2015 11:54:45 GMT

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.

Re: SQL exists in Qlikview

Anonymous — Sun, 11 Jan 2015 12:06:07 GMT

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?

Re: SQL exists in Qlikview

Peter_Cammaert — Sun, 11 Jan 2015 12:18:32 GMT

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;

Re: SQL exists in Qlikview

Anonymous — Sun, 11 Jan 2015 13:30:49 GMT

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?

Re: SQL exists in Qlikview

Peter_Cammaert — Sun, 11 Jan 2015 13:50:37 GMT

It was a guess. Someone has to fetch the data from a database. Inserting a QVD buffer in your data flow is only useful if you either plan to use the QVD in other documents, or you need the source table in the same document many times.

If this is the only time you will be reading the source table, you can do it with this very simple SELECT on the source DB. The overhead on the RDBMS is very low, even for millions of rows.

If your situation is different, replace the SQL SELECT with a sub-second QVD read like:

TempFact:

LOAD * FROM [SourceTable.qvd] (qvd); // Optimized LOAD = very fast

Best,

Peter

Re: SQL exists in Qlikview

Anonymous — Sun, 11 Jan 2015 14:15:46 GMT

Thank You