topic Group by > invalid expression in QlikView

Group by > invalid expression

Fri, 05 Jun 2015 09:47:34 GMT

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;

Re: Group by > invalid expression

marcus_sommer — Fri, 05 Jun 2015 09:49:38 GMT

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

- Marcus

Re: Group by > invalid expression

rubenmarin — Fri, 05 Jun 2015 09:49:54 GMT

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;

Re: Group by > invalid expression

awhitfield — Fri, 05 Jun 2015 09:50:11 GMT

You need

max(date) as date

HTH Andy

Re: Group by > invalid expression

MayilVahanan — Fri, 05 Jun 2015 09:52:35 GMT

Try like this

table2:

LOAD

category,

name,

max(date) as date

resident table1 group by category, name order by date;

Re: Group by > invalid expression

Anonymous — Fri, 05 Jun 2015 09:53:49 GMT

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

Re: Group by > invalid expression

jonathandienst — Fri, 05 Jun 2015 09:54:03 GMT

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;