Help with Group By

[Anonymous]

Hi All

I am trying to load some data from a sales order table and i am having trouble with 'group by'.  Each order number has several lines and each line can have a different status.  I want a field that shows the minimum status for each order and this is the load script I am using:

SalesDetail:

LOAD

  SDDOC as [Document Number],

  SDAEXP/100 as GSV,

  SDAN8 as [Customer Number],

  SDDOCO as [Order Number],

  SDDRQJ as [Request Date],

  SDITM as F4101PrimaryKey,

  SDLNID as [Line Number],

  SDLPRC/10000 as [Unit Price],

  SDLTTR as [Last Status],

  SDNXTR as [Next Status],

  SDSHAN as [Ship To],

  SDQTYT as [Quantity Sold],

  min(SDLTTR) as MinStatus

FROM

(qvd)

where SDDRQJ > 116366 and SDLTTR < 900 and SDCO = '00400'

group by SDDOCO;

However I keep getting an 'Invalid Expression' error.  What am I doing wrong?

Thanks

A.

[sunny_talwar · Accepted solution]

May be try this:

SalesDetail:

LOAD

  SDDOC as [Document Number],

  SDAEXP/100 as GSV,

  SDAN8 as [Customer Number],

  SDDOCO as [Order Number],

  SDDRQJ as [Request Date],

  SDITM as F4101PrimaryKey,

  SDLNID as [Line Number],

  SDLPRC/10000 as [Unit Price],

  SDLTTR as [Last Status],

  SDNXTR as [Next Status],

  SDSHAN as [Ship To],

  SDQTYT as [Quantity Sold]

FROM

(qvd)

where SDDRQJ > 116366 and SDLTTR < 900 and SDCO = '00400';

Left Join (SalesDetail)

LOAD SDDOCO,

  Min(SDLTTR) as MinStatus

Resident SalesDetail

Group By SDDOCO;