Group by > invalid expression

[#1]

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;

[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]

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;

[Anonymous · Accepted solution]

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]

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;