Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Mode() is an Aggregation Functions and needs group by.
BR
Ariel
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 ;
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;
HEy,
THanks it works but why didn't it work in my script. Even i am almost using in same manner.
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;
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
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
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
Hey,
But i have used mode() on name only and used group by name. THen why it is showing error in my script above ??