topic LOAD syntax question in QlikView

LOAD syntax question

Wed, 08 Aug 2012 17:00:00 GMT

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?

Re: LOAD syntax question

bgerchikov — Wed, 08 Aug 2012 17:17:22 GMT

Hi Kevin,

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

Hope it will hellp.

Re: LOAD syntax question

jonathandienst — Wed, 08 Aug 2012 17:53:24 GMT

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