topic Re: Writing SQL scripts in qlikview editor in QlikView

Writing SQL scripts in qlikview editor

Anonymous — Wed, 08 Apr 2015 12:34:22 GMT

Hi,

i have imported a few tables (by importing excel files) in a qlikview script and want to perform some more tasks such as aggregations at various levels.

Do I need to use the Load syntax or is there a way to use the SQL syntax ?

For example, I would like to write the following SQL:

Create table XXX as

select country, sum(sales)

from YYY

where item_code in (Select item_code from ZZZ)

group by country;

Re: Writing SQL scripts in qlikview editor

p_verkooijen — Wed, 08 Apr 2015 15:21:47 GMT

You don't need to aggregate your data on the script level, you could create a straight table, using country as the dimension and a expression containing SUM(sales).

Other levels of aggregation can be displayed using a other dimension than country (or use a cycle group)

That beeing said:

You can't use GROUP BY on a Excel file.

Use a RESIDENT instead (first load the data from the Excel in a temp table)

Temp_Sales

LOAD *

FROM YYY.xls ......

WHERE.....

Sales:

LOAD

country, sum(sales) as salestotal

RESIDENT Temp_Sales

GROUP BY country;

DROP TABLE Temp_Sales;

Re: Writing SQL scripts in qlikview editor

rajkumarb — Wed, 08 Apr 2015 15:22:09 GMT

Try This

Table NameXXX:

Load

country

sum(sales)

item_code

SQL

select , sum(sales)

from YYY

where item_code in (Select item_code from ZZZ)

group by country ;

Re: Writing SQL scripts in qlikview editor

Colin-Albert — Wed, 08 Apr 2015 15:22:23 GMT

To load data into QlikView you need to use QlikView syntax.

There is no need to use SQL commands to create tables in a SQL data base, just load the data into QlikView.

You could use a QlikView script something like this. Remember QlikView is case sensitive!

XXX:

load

country,

total_Sales ;

SQL select country, sum(sales) as total_sales

from YYY

where item_code in (Select item_code from ZZZ)

group by country;

To sum data from Excel you will need to use a temp table & resident load as suggested earlier

Re: Writing SQL scripts in qlikview editor

anbu1984 — Thu, 09 Apr 2015 05:27:56 GMT

ZZZ:

Load item_code As ItmCd from ZZZ.xlsx;

XXX:

Load country, sum(sales) where Exists(ItmCd,item_code) group by country;

Load country,sales,item_code from YYY.xlsx;

Re: Writing SQL scripts in qlikview editor

Anonymous — Sun, 12 Apr 2015 09:56:47 GMT

Thanks guys.

Colin-Actually, I intended to use SQL because i am new to Qlikview and want to continue using the age old SQL. Like R has 'sqldf' package and SAS has 'Proc SQL' facility where you can write SQL commands. I was looking for something like that. Is there any facility in Qlikvkiew?

Re: Writing SQL scripts in qlikview editor

anbu1984 — Sun, 12 Apr 2015 11:31:21 GMT

No you can't use SQL to read Excel files

Re: Writing SQL scripts in qlikview editor

Anonymous — Sun, 12 Apr 2015 11:41:50 GMT

You can use SQL only when you read data from the relational databases.. E.g.

XXX:

SQL SELECT

country,

sum(sales) as CountrySales

from YYY

where item_code in (Select item_code from ZZZ)

group by country;

If a data source is Excel or any other file, you have to use QV script - which is rather similar to SQL anyway:

ZZZ:

LOAD DISTINCT item_code from ZZZ;

XXX:

LOAD

country,

sum(sales) as CountrySales

from ...

where exists( item_code)

group by country;

DROP TABLE ZZZ;