topic Re: How could we use Mapping LOAD from MySQL(ODBC Connection) in QlikView

How could we use Mapping LOAD from MySQL(ODBC Connection)

Sat, 19 Mar 2016 15:26:57 GMT

When i connect to Mysql DB from Qlikview (using ODBC 32) it works fine, But when i use Mapping LOAD from Mysql Table it shows

Cannot Open file filepath\InventoryDb.Orders System Can't find the file specified error generate. Actually there is no such location like filepath\ InventoryDb.Orders. Qlikview wrongly assuming my MySQL table as file.What i do to resolve this.

My Script is like this:

ODBC CONNECT TO InventoryDSN; //DSN i used to connect MySQL DB

Also i tried OEDB Provider for ODBC

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Data Source=InventoryDSN;Extended Properties="DSN=InventoryDSN;";Initial Catalog=InventoryDb];

User_ID_to_EnteredByMap:

Mapping LOAD

id as user_id,

username

FROM InventoryDb.Orders;

Re: How could we use Mapping LOAD from MySQL(ODBC Connection)

swuehl — Sat, 19 Mar 2016 15:40:45 GMT

Try

Mapping

SELECT

id as user_id,

username

FROM InventoryDb.Orders;

Re: How could we use Mapping LOAD from MySQL(ODBC Connection)

Sat, 19 Mar 2016 15:49:25 GMT

It throws table doesn't exist error

Re: How could we use Mapping LOAD from MySQL(ODBC Connection)

swuehl — Sat, 19 Mar 2016 23:00:07 GMT

Have you tried using the table wizard to just load this table from your DB as a resident table into your data model?

As soon as this works out, creating a mapping table should be easy by adding the MAPPING LOAD prefix.

Re: How could we use Mapping LOAD from MySQL(ODBC Connection)

Sun, 20 Mar 2016 07:12:16 GMT

Sorry that was my mistake

User_ID_to_EnteredByMap:

Mapping LOAD

id as user_id,

username

FROM InventoryDb.Orders; (I used this as we Mapping Load from QVD files with out using the SQL SELECT )

the new Correct script is like this

User_ID_to_EnteredByMap:

Mapping LOAD

id as user_id,

username; SQL SELECT *

FROM InventoryDb.Orders;

now the problem is ok, and one question is there any performance issues while i use

SQL SELECT *

FROM InventoryDb.Orders; in Mapping LOAD

Re: How could we use Mapping LOAD from MySQL(ODBC Connection)

swuehl — Sun, 20 Mar 2016 11:40:44 GMT

You need to SELECT only the fields in the SQL statement that you later use in the preceding LOAD statement.

If the DB table doesn't change (often), you can consider storing the table to a QVD to speed up the daily load script. You can look into a multi-staged load process (like QVD generators, data model, dashboard) then.