Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Group by > invalid expression

Hello,

I am trying to get the maximum value per category, but somehow I cannot get my script working. I refered to this post: Max Group By 2 fields. The solution that is posted there should work for my case as well, but I get an invalid expression error.

I load a table that has all file names in a certain folder and their respective creation dates (which I extracted from the file names). Every report in the folder belongs to a certain category. This category can be derived from the last bit of the file name. I want to create a field that gives the maximum date per category. I used the following script, but that thus leads to the invalid expression error. Does anyone have a clue what might be the problem?

Set NullInterpret = '';

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='MM-DD-YYYY';

SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

LET vPath = 'C:\Users\yno\Documents\Qlikview-Dashboard\';

LET vToday = date(today(),'DDMMYYYY');

LET vinputPositions = 'OTC-Posities';

LET vinputDTCCFX = 'DTCCFX';

LET vinputDTCCRATES = 'DTCCRATES';

LET vinputDTCCCOMMODITIES = 'DTCCCOMMODITIES';

LET vinputDTCCCREDIT='DTCCCREDIT';

table1:

load

FileName() as name,

date(Mid(Filename(),findoneof(Filename(),'-')+1,10),'MM-DD-YYYY') as date,

right(Filename(),7) as category

from $(vPath)*.csv;

table2:

LOAD

category,

name,

max(date)

resident table1 group by category order by date;

drop table table1;

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

you Need to Change table2:

table2:

noconcatenate load

category,

Name,

max(date)

resident table1 Group by category, Name order by date

drop table1

or if you only Need max Dates for categorys, drop the field Name

View solution in original post

6 Replies
marcus_sommer

You need the field "name" also within the group by statement.

- Marcus

rubenmarin

Hi Martijn, you need to add all fields outside an aggregation function to the grou by clause, in this case:

LOAD

  category,

name,

max(date)

resident table1 group by category, name order by date;

awhitfield
Partner - Champion
Partner - Champion

You need

max(date) as date

HTH Andy

MayilVahanan

Hi

Try like this

table2:

LOAD

category,

name,

max(date) as date

resident table1 group by category, name order by date;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Anonymous
Not applicable
Author

you Need to Change table2:

table2:

noconcatenate load

category,

Name,

max(date)

resident table1 Group by category, Name order by date

drop table1

or if you only Need max Dates for categorys, drop the field Name

jonathandienst
Partner - Champion III
Partner - Champion III

You need to add name to your group by as well. All non-aggregated fields must be in a group by clause. And you cannot sort by date, because it is an aggregated field.

Why do you need to sort the output? Use this and sort the date in the front end:

load

category,

name,

max(date) As mDate

resident table1

group by category, name;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein