Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

error when loading through ODBC

Per student per schoolyear we add a new record. This record contains  (among toher fields) the date of arrival.

Mostly date of arrival equals the start of the schoolyear.

For a report I need to have per student the first date of arrival.

Data is loaded through an ODBC connection from a SQL-server

When I use this script :

FirstDayOfArrival :

Load Key_Studentnumber,

Min(DateOfArrival) as FirstDayOfArrival Group By Key_Studentnumber;

SQL SELECT DateOfArrival,

  DepartmentCode,

  Studentnumber as Key_Studentnumber

FROM CompDB.dbo.Students;

I don't get an error

However, when I want to add the departmentcode for example, I get an error saying : ODBC read failed SQL Select DateOfArrival ......(rest of SQL script)

Script that gives error

FirstDayOfArrival :

Load Key_Studentnumber,

DepartmentCode,,

Min(DateOfArrival) as FirstDayOfArrival Group By Key_Studentnumber;

SQL SELECT DateOfArrival,

  DepartmentCode,

  Studentnumber as Key_Studentnumber

FROM CompDB.dbo.Students;

What do I have to change ?

4 Replies
Highlighted
Creator III
Creator III

It looks like you have two commas behind DepartmentCode

Script that gives error

FirstDayOfArrival :

Load Key_Studentnumber,

DepartmentCode,,      <--- too many commas

Min(DateOfArrival) as FirstDayOfArrival Group By Key_Studentnumber;

Highlighted
Master III
Master III

try with a resident load

FirstDayOfArrival :

SQL SELECT DateOfArrival,

  DepartmentCode,

  Studentnumber as Key_Studentnumber

FROM CompDB.dbo.Students;

NoConcatenate

final:

Load Key_Studentnumber,

DepartmentCode,

Min(DateOfArrival) as FirstDayOfArrival

Resident FirstDayOfArrival

Group By Key_Studentnumber;

drop Table FirstDayOfArrival;

hth

Sasi

Highlighted

All non-aggregated fields need to be in the group by (and remove the extra comma):

FirstDayOfArrival :

Load Key_Studentnumber,

DepartmentCode,

Min(DateOfArrival) as FirstDayOfArrival

Group By Key_Studentnumber, DepartmentCode;

SQL SELECT DateOfArrival,

  DepartmentCode,

  Studentnumber as Key_Studentnumber

FROM CompDB.dbo.Students;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Creator II
Creator II

Thnx to all for your quick response. I tried jonathans's script and that worked. I will look at the others too.

The double comma was a typo .

Strange that all the other fields have to be in the group by

I want to load much more fields from that table, but when this is necessary I think I use an extra table