topic Re: Error in load script! in QlikView

Error in load script!

nikhilgarg — Mon, 22 Sep 2014 11:20:56 GMT

Hey,

I am using mode() as follows but error is showing invalid expression.

Please help.

-----------------------------

Directory;

LOAD Id,

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year,

ModifiedTime,

mode(Name) as CommonName

FROM

[Disaster_Data\Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster) Group By Name ;

Thnks

Re: Error in load script!

ariel_klien — Mon, 22 Sep 2014 11:24:16 GMT

Hi,

Mode() is an Aggregation Functions and needs group by.

Ariel

Re: Error in load script!

Mon, 22 Sep 2014 11:28:11 GMT

try like this

Directory;

LOAD

Name,

mode(Name) as CommonName

FROM

[Disaster_Data\Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster) Group By Name ;

Re: Error in load script!

Anonymous — Mon, 22 Sep 2014 11:32:08 GMT

you can't use aggregation with Excel files.

You need to load data to the table and then make an aggregation.

Something like this:

T1:

LOAD Id,

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year,

ModifiedTime,

Name

FROM

[Disaster_Data\Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster);

T2:

Load

Id,

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year,

ModifiedTime,

mode(Name) as CommonName

Resident T1

Group By

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year,

ModifiedTime;

Re: Error in load script!

nikhilgarg — Mon, 22 Sep 2014 11:33:56 GMT

HEy,

THanks it works but why didn't it work in my script. Even i am almost using in same manner.

Re: Error in load script!

Mon, 22 Sep 2014 11:38:16 GMT

because ,

since Group by use for aggregation function,

and if you use group by Name,

then you can use only Name field, but can use multiple aggrigated field.

example.

load

sum(C) from table

group by A,B;

Re: Error in load script!

sathishkumar_go — Mon, 22 Sep 2014 11:40:48 GMT

HI Nikhil,

You have to include all the fileds in Group by functon.

LOAD Id,

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year,

ModifiedTime,

mode(Name) as CommonName

FROM

[Disaster_Data\Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster) Group By Name, StartDate,EndDate,Country,Type,Sub_Type,Name,Killed,Injured,Year,ModifiedTime ;

-Sathish

Re: Error in load script!

adhudson — Mon, 22 Sep 2014 11:45:43 GMT

Hi,

As I referred, Mode is a function which can only be used inside the charts to get most commonly occurring values in a field.

The below is the definition of the function which I took from QlikView help content.

mode([{set_expression}][ distinct ] expression)

Returns the mode value, i.e. the most commonly occurring value, of expression or field iterated over the chart dimension(s). If more than one value is equally commonly occurring, NULL is returned. Mode can return numeric values as well as text values.

I doubt whether this function can be used in scripts.!!!

Regards,

Andrew Hudson

Re: Error in load script!

sathishkumar_go — Mon, 22 Sep 2014 11:53:32 GMT

Hi Andrew,

We can use a mode function in script.

Please refer the help in qlikview

Returns the mode value, i.e. the most commonly occurring value, of expression over a number of records, as defined by a group by clause. If more than one value is equally commonly occurring, NULL is returned. Mode can return numeric values as well as text values.

Examples:

Load Month, mode( ErrorNumber ) as MostCommonErrorNumber from abc.csv group by Month;

Load Month, mode( Product ) as ProductMostOftenSold from abc.csv group by Month;

-Sathish

Re: Error in load script!

nikhilgarg — Mon, 22 Sep 2014 11:55:36 GMT

Hey,

But i have used mode() on name only and used group by name. THen why it is showing error in my script above ??

Re: Error in load script!

sathishkumar_go — Mon, 22 Sep 2014 12:07:40 GMT

Hi Nikhil,

In your script you have added "Name" only in group by function.

whenever you are using group by function you have to add all the fields which you have used in load script.

-Sathish

Re: Error in load script!

maxgro — Mon, 22 Sep 2014 12:18:03 GMT

mode works in script and you can group by reading from an excel

you have to group by for all the fields without an aggr function

load c1, c2,

mode(Name)

FROM a.xlsx (ooxml, embedded labels, table is a)

group by c1, c2;

load c1 as c1new,

mode(Name)

FROM a.xlsx (ooxml, embedded labels, table is a)

group by c1;

Re: Error in load script!

anbu1984 — Mon, 22 Sep 2014 12:23:59 GMT

Without using group by

Table:

LOAD Id,

StartDate,

EndDate,

Country,

Type,

Sub_Type,

Name,

Killed,

Injured,

Year,

ModifiedTime

FROM

[Disaster_Data\Copy of Disaster.xlsx]

(ooxml, embedded labels, table is Disaster) ;

Left Join(Table)

Load mode(Name) as CommonName Resident Table;

Re: Error in load script!

nikhilgarg — Tue, 23 Sep 2014 04:37:30 GMT

Hey,

THanks but is this constraint for group by in QLikview only ?? Becoz as far as i know no such constraint in sql....

Re: Error in load script!

anbu1984 — Tue, 23 Sep 2014 05:41:42 GMT

You have same constraints in Sql which can be avoided by using Analytic functions

Re: Error in load script!

sathishkumar_go — Tue, 23 Sep 2014 11:28:51 GMT

Hi Konstantins,

We can use aggregation function with excel files

-Sathish