6 Replies Latest reply: Jun 5, 2015 5:54 AM by Jonathan Dienst RSS

    Group by > invalid expression

    Martijn Noorda

      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;