topic error when loading through ODBC in QlikView

error when loading through ODBC

curiousfellow — Mon, 24 Aug 2015 07:58:10 GMT

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 ?

Re: error when loading through ODBC

quwok — Mon, 24 Aug 2015 08:13:45 GMT

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;

Re: error when loading through ODBC

sasiparupudi1 — Mon, 24 Aug 2015 08:16:11 GMT

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

Re: error when loading through ODBC

jonathandienst — Mon, 24 Aug 2015 08:23:21 GMT

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;

Re: error when loading through ODBC

curiousfellow — Mon, 24 Aug 2015 08:55:21 GMT

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