LOAD syntax question

[#1]

I added the line starting with "FIRSTSORTEDVALUE" to my script.

LOAD

If(IsNull(Opp_StartDate),'1/1/1989',Year(Opp_StartDate)) As OppStYear,

MonthName(Opp_StartDate) As tStartMonth,

MonthName(Opp_ClosedDate) As tCloseMonth,

FirstSortedValue(Opp_StartDate,-Opp_StartDate) As NewestOpp Group by Company

* ;

OLEDB Connect to (connection string)

SELECT

(field list)

FROM (filename)

And this now generates a SQL error. "can't find field <*>"

I know my syntax is wrong, but I can't find anything in the documentation to fix it! Note that if I comment out the newly added line, everything runs perfectly, so I know the problem is there.

Any thoughts?

[bgerchikov]

Hi Kevin,

Group by clause must include actual field name. You have "*" right after "Company" with no comma.

Hope it will hellp.

[jonathandienst]

Hi

Apart from the missing comma before the *, FirstSortedValue is an aggregate function and cannot be used the way you are attempting. The group by statement applies to the whole load, not just the line you entered. You could do something like this after completing the first load (let's give that table a label of Table1):

OLEDB Connect to (connection string);
Table1:
LOAD
          If(IsNull(Opp_StartDate),'1/1/1989',Year(Opp_StartDate)) As OppStYear,
          MonthName(Opp_StartDate) As tStartMonth,
          MonthName(Opp_ClosedDate) As tCloseMonth,
* ;
SQL SELECT
(field list)
FROM (sql table name);
Join (Table1)
LOAD Company,
     Max(OppStartDate) As NewestOpp
Resident Table1
Group By Company;

Hope that helps

Jonathan