topic Re: Load Script - MS Access & Excel in QlikView

Load Script - MS Access & Excel

Fri, 20 Sep 2013 05:28:08 GMT

Hello Everyone.

I am loading data from tables in MS Access. I needed to obtain information on how to undertake following:

* Add New Column/Field for Calcuation based on existing data e.g.

**Sales = Unit Price X Quantity, Where Unit Price & Quantity are part of the Table in MS Access

I attempted to do Sales, and following is the my load script for MS Access:

ODBC CONNECT TO [MS Access Database;DBQ=C:\Users\Uday\Desktop\Qlikview Project\Files\QWT.mdb];
//-------- Start Multiple Select Statements ------

LOAD
[OrderId],
[ProductId],
[UnitPrice],
[Quantity],
[Discount],
([Unit Price*Quantity]) as [Sales];

SQL SELECT
[OrderId],
[ProductId],
[UnitPrice],
[Quantity],
[Discount],
FROM `Order Details`;
//-------- End Multiple Select Statements ------

As you can see from above, I have attempted to add a new column with following:

([Unit Price*Quantity]) as [Sales];

However, when I run the script I get the following error message:

SQL SELECT

[OrderId],

[ProductId],

[UnitPrice],

[Quantity],

[Discount],

FROM `OrderDetails`

Please note the following:

* I am using Qlikview Version 11

* I searched Qlikkview community & blogs to determine a possible solution, however couldn't locate anythign very specific

I believe this should be a very a basic coding in the script that should be very easily achievable. Your expert help, guidance is welcomed.

Thank You

Uday

Re: Load Script - MS Access & Excel

jonathandienst — Fri, 20 Sep 2013 05:59:21 GMT

Uday

Remove the comma after [Discount]
Your calc expression (last line of the LOAD) should read:

UnitPrice * Quantity As Sales;

Regards

Jonathan

Re: Load Script - MS Access & Excel

rajeshvaswani77 — Fri, 20 Sep 2013 06:04:25 GMT

Hi Uday,

You have to use preceding load as below to achieve this.

LOAD *,

[UnitPrice] * [Quantity] as [Sales];

LOAD
[OrderId],
[ProductId],
[UnitPrice],
[Quantity],
[Discount],
([Unit Price*Quantity]) as [Sales];

SQL SELECT
[OrderId],
[ProductId],
[UnitPrice],
[Quantity],
[Discount],
FROM `Order Details`;

Thanks,

Rajesh Vaswani

Re: Load Script - MS Access & Excel

Fri, 20 Sep 2013 06:22:24 GMT

Hi Uday,

Few things which seem incorrect in your syntax :

1. Remove the inverted commas from table name Order Details.

2. Remove comma after Discount in select statement.

3. ([Unit Price*Quantity]) as [Sales] should be ([Unit Price] * [Quantity]) as [Sales]

Hope this helps!

Re: Load Script - MS Access & Excel

Fri, 20 Sep 2013 15:52:45 GMT

Thank you everyone for responding. however this is not working and I have tried the following options for loading from MS Access database table. The ` you see in the order details is defaulted from script and removing it could issues. So anyway, I tried the following two scripts:

SCRIPT A:

LOAD
[OrderId],
[ProductId],
[UnitPrice],
[Quantity],
[Extension],
[Discount],
([Unit Price*Quantity]) as [Sales];

SQL SELECT
[OrderId],
[ProductId],
[UnitPrice],
[Quantity],
[Extension],
[Discount]
([Unit Price*Quantity]) as [Sales],
FROM `Order Details`;

SCRIPT B:

LOAD *,

[UnitPrice] * [Quantity] as [Sales];

LOAD
[OrderId],
[ProductId],
[UnitPrice],
[Quantity],
[Discount],
([Unit Price*Quantity]) as [Sales];

SQL SELECT
[OrderId],
[ProductId],
[UnitPrice],
[Quantity],
[Discount],
FROM `Order Details`;

IN BOTH TH ESCRIPTS I GET THE FOLLOWING ERROR MESSAGE;

SQL##f - SqlState: 37000, ErrorCode: 4294963792, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
SQL SELECT
[OrderId],
[ProductId],
[UnitPrice],
[Quantity],
[Extension],
[Discount]
([Unit Price*Quantity]) as [Sales],
FROM `Order Details`

COULD SOMEONE PLEASE LET ME KNOW WHAT IS MISSING IN THE LOADSCRIPT

Re: Load Script - MS Access & Excel

jaimeaguilar — Fri, 20 Sep 2013 16:03:46 GMT

Hi,

Try with this, deleting the mentioned line and where it says SQL SELECT, if you fetching everything it's easier if you type * instead of the explicit field names. BTW, brackets [ ] are mandatory only if your field name has spaces or certain alphanumeric characters, otherwise is optional.

[Table_Name]:

LOAD *,

[UnitPrice] * [Quantity] as [Sales];

LOAD
[OrderId],
[ProductId],
[UnitPrice],
[Quantity],
[Discount],
//([Unit Price*Quantity]) as [Sales]; JUST COMMENT OR DELETE THIS LINE

SQL SELECT *
FROM `Order Details`;

regards

Re: Load Script - MS Access & Excel

jagan — Fri, 20 Sep 2013 16:16:58 GMT

Hi Uday,

Try this script

SELECT

OrderId,

ProductId,

UnitPrice,

Quantity,

Extension,

Discount,

("Unit Price"*Quantity) as Sales

FROM "Order Details";

Hope this helps you.

Regards,

Jagan.

Re: Load Script - MS Access & Excel

Fri, 20 Sep 2013 16:19:08 GMT

Thank you for the response

What the meaning of the statement, ie should it be as you have mentioned or more like name of the table I want or name of the table in ms access database?

Thank you.

Re: Load Script - MS Access & Excel

Fri, 20 Sep 2013 16:22:52 GMT

I have entered the following script:

[Order Details]:

LOAD *,
[UnitPrice] * [Quantity] as [Sales];

LOAD
[OrderId],
[ProductId],
[UnitPrice],
[Quantity],
[Discount];
//([Unit Price*Quantity]) as [Sales];
SQL SELECT *
FROM `Order Details`;

BUT NOW I GET THE FOLLOWING ERROR MESSAGE, EVEN THOUGH THE FIELD IS IN THE DATABASE TABLE:

Field not found - <OrderId>

SQL SELECT *

FROM `Order Details`

Re: Load Script - MS Access & Excel

jaimeaguilar — Fri, 20 Sep 2013 16:23:24 GMT

the [Table_Name] is the name of the table inside QlikView, you can either rename it or leave it blank. If you don't explicitly put a name inside QlikView, it will take the name from the original database (Access in this case). However, as a best practice is recommended to always put the name of the table explicitly inside QlikView. It has to be before the LOAD statement and ending the name with :

regards

Re: Load Script - MS Access & Excel

jagan — Fri, 20 Sep 2013 16:35:39 GMT

Hi Uday,

Check whether the Case of the field name differs, this may also the issue.

Regards,

Jagan.

Re: Load Script - MS Access & Excel

Fri, 20 Sep 2013 16:49:04 GMT

BIG BIG THANK YOU TO EVERYONE FOR YOUR EXCELLENT HELP THANK YOU