topic Navision 4.0 ODBC in QlikView

Navision 4.0 ODBC

Tue, 08 Jun 2010 12:09:49 GMT

Hello!

I have to get data from Navision 4.0 native database. I use the ODBC which came along with NAV installation.

I can connect to the NAV database, see tables and fields... I created a simpe query with the designer...

LOAD "Entry No_",
"Item No_",
"Posting Date";
SQL SELECT "Entry No_",
"Item No_",
"Posting Date"
FROM "Value Entry";

LOAD "No_";
SQL SELECT "No_"
FROM Item;

Value Entry data loads fine... But Item data fails...

SQL Error:[Simba][SimbaEngine ODBC Driver]Invalid column name: 'No_'.
SQL Scriptline:
SQL State:S0022
SQL SELECT "No_"
FROM Item

For a SIB I simply copied all required tables to an Excel file an load from there. But it isn't a solution in a long term...

So... How to get data from Navision?

Navision 4.0 ODBC

Tue, 08 Jun 2010 12:39:48 GMT

You have license to extract data from navision first check

then you install CODBC navision data driver

then make DSN

After making DSN call this DSN in Qlikview ODBC trough.

Regards

Ashish

Navision 4.0 ODBC

Tue, 08 Jun 2010 12:58:29 GMT

Thank you!

I will try that...

Navision 4.0 ODBC

Tue, 08 Jun 2010 13:07:40 GMT

I think name of Item No_ is

different in back end navision please check?

Regards

Ashish

Navision 4.0 ODBC

Tue, 08 Jun 2010 13:40:41 GMT

Here is a part of Field list of Item table.

Enabled Field No. Field Name Data Type Length
Yes 1 No. Code 20
Yes 2 No. 2 Code 20
Yes 3 Description Text 30

And from Value Entry table...

Enabled Field No. Field Name Caption Data Type Length
Yes 1 Entry No. Entry No. Integer
Yes 2 Item No. Item No. Code 20
Yes 3 Posting Date Posting Date Date

No real difference...

Navision 4.0 ODBC

Tue, 08 Jun 2010 14:37:13 GMT

If you're connecting directly to SQL then SQL converts '.' on a field name to '_' (e.g. 'No.' in NAV is 'No_' in SQL). If you're using the CODBC driver then you should be able to reference the field names as you see them in NAV.