Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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;

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

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
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