Writing SQL scripts in qlikview editor

[Anonymous]

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;

[p_verkooijen]

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]

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 ;

[Colin-Albert]

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]

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;

[Anonymous]

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]

No you can't use SQL to read Excel files

[Anonymous]

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;