Error in load script!

[nikhilgarg]

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

[ariel_klien]

Hi,

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

BR

Ariel

[#3]

hi

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 ;

[Anonymous]

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;

[nikhilgarg]

HEy,

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

[#6]

hi

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

A,

B,

sum(C) from table

group by A,B;

[sathishkumar_go]

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

[adhudson]

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

[sathishkumar_go]

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

[nikhilgarg]

Hey,

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