topic All records are not loading from access table when loading into qvw? in QlikView

All records are not loading from access table when loading into qvw?

Mon, 16 Jun 2014 09:24:09 GMT

Hi,

I have ms access database tablw which have records 60,000 when I am loding into qvw it is sloading only 12,000 records. can anyone please suggest why there is less count of records loading.

Thanks.

Re: All records are not loading from access table when loading into qvw?

alexandros17 — Mon, 16 Jun 2014 09:27:40 GMT

There must be an unrecognized character that stop the loading ...

Re: All records are not loading from access table when loading into qvw?

Mon, 16 Jun 2014 09:28:25 GMT

How can I know this?

Re: All records are not loading from access table when loading into qvw?

ashfaq_haseeb — Mon, 16 Jun 2014 09:28:44 GMT

Hi, try by adding flag to your load script.

load

F1,

F2,

1 as Flag,

F3;

from <AccessDB>.

Hope it helped.

Regards

ASHFAQ

Re: All records are not loading from access table when loading into qvw?

Mon, 16 Jun 2014 09:42:52 GMT

Thanks. tried still loading 12,000

Re: All records are not loading from access table when loading into qvw?

ngulliver — Mon, 16 Jun 2014 09:49:31 GMT

Do you get an error message or does it simply stop ?

Were you able to identify the last record that was read in successfully?

Re: All records are not loading from access table when loading into qvw?

Mon, 16 Jun 2014 10:08:38 GMT

it is showing ODBC read failed when I am loading all columns in the table to qvd

Re: All records are not loading from access table when loading into qvw?

ashfaq_haseeb — Mon, 16 Jun 2014 10:15:13 GMT

Hi,

Try by connecting OLEDB connection.

Did you enabled document log?

what does log says?

Regards

ASHFAQ

Re: All records are not loading from access table when loading into qvw?

Mon, 16 Jun 2014 10:34:16 GMT

Hi, Amelia2000

You can upload your log for our investigation.

Regards,

Alan

Re: All records are not loading from access table when loading into qvw?

Mon, 16 Jun 2014 11:00:34 GMT

please see attached.

Re: All records are not loading from access table when loading into qvw?

ashfaq_haseeb — Mon, 16 Jun 2014 11:02:49 GMT

Hi you need to cast date in proper format.

Can you post few lines of data in an excel.

Regards

ASHFAQ

Re: All records are not loading from access table when loading into qvw?

Mon, 16 Jun 2014 11:58:36 GMT

Please see attached.

Re: All records are not loading from access table when loading into qvw?

Mon, 16 Jun 2014 12:01:22 GMT

Amelia, there is a way to narrow down the cause. For loading this table, replace load statement with load * on a test QWVs. See if there is 60,000 loaded into your app.

If yes, you would know this is because of the transformation issue from load statement. Check columns one by one to see which transformation cause this issue. As it describe at your uploaded image, it's because that your date function is invalid on specific columns, which may result in data missing.

Further, check if there is where condition to filter the data, and search if there is keywords like "limit", "First", which you haven't excepted yet.

Re: All records are not loading from access table when loading into qvw?

ashfaq_haseeb — Mon, 16 Jun 2014 12:03:49 GMT

Hi,

Try by casting date for all date field.

Like below

load

F1,

date(date#(Datefield1,'DD/MM/YYYY'),'MM/DD/YYYY') as Date1,

date(date#(Datefield2,'DD/MM/YYYY'),'MM/DD/YYYY') as Date2,

F4;

from <accessDB>

Replace 'DD/MM/YYYY' with proper date format in database

and replace 'MM/DD/YYYY' with your required format.

Hope it helped.

Regards

ASHFAQ

Re: All records are not loading from access table when loading into qvw?

Mon, 16 Jun 2014 12:55:37 GMT

I tried the same but still showing same and there is three rows with Chinese language would that create any problem?

Re: All records are not loading from access table when loading into qvw?

ashfaq_haseeb — Mon, 16 Jun 2014 12:57:23 GMT

Hi try to load by commenting chines fields and check if that can help.

Regards

ASHFAQ

Re: All records are not loading from access table when loading into qvw?

Mon, 16 Jun 2014 13:06:06 GMT

when I am using below it is showing error as syntax in correct. The below ID have Chinese language

Table:

LOAD

ID,

...

....

....;

SQL SELECT *

FROM data;

where ID<>'453015579' and ID<>'20857';

Re: All records are not loading from access table when loading into qvw?

ashfaq_haseeb — Mon, 16 Jun 2014 13:09:47 GMT

Hi try below

Table:

LOAD

ID,

...

....

....;

SQL SELECT *

FROM data;

where ID not in ('453015579' ,'20857');

Regards

ASHFAQ

Re: All records are not loading from access table when loading into qvw?

Mon, 16 Jun 2014 13:11:24 GMT

I tried it is showing data type mismatch in criteria expression

Re: All records are not loading from access table when loading into qvw?

ashfaq_haseeb — Mon, 16 Jun 2014 13:17:13 GMT

Hi try below

Table:

LOAD

ID,

...

....

....;

SQL SELECT *

FROM data;

where ID not in (453015579 ,20857);

Regards

ASHFAQ