Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
ashmeetthukral
New Contributor II

Writing SQL scripts in qlikview editor

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;

7 Replies
p_verkooijen
Valued Contributor

Re: Writing SQL scripts in qlikview editor

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;

rajkumarb
Contributor II

Re: Writing SQL scripts in qlikview editor

HI

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

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

anbu1984
Honored Contributor III

Re: Writing SQL scripts in qlikview editor

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;

ashmeetthukral
New Contributor II

Re: Writing SQL scripts in qlikview editor

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?

anbu1984
Honored Contributor III

Re: Writing SQL scripts in qlikview editor

No you can't use SQL to read Excel files

mov
Esteemed Contributor III

Re: Writing SQL scripts in qlikview editor

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;

Community Browser