topic Re: How many record can be loaded from CSV file in QlikView

How many record can be loaded from CSV file

Fri, 06 Dec 2013 01:08:09 GMT

Dear all.

I am using the following code to load the data into qlikview from a csv file. I have 40k+ records in the CSV. But when i display the data in a table box. It only give me 19k+ records.

May i know is there any limitation on this or on the table box itself? How do i count the total number of record as well?

Directory;

LOAD D_PO,

D_PARTNO,

D_DESCRIPTION,

D_UNIT_COST,

D_ACCEPTED_QTY,

D_ORDER_PLACED,

D_YEAR,

D_PERIOD,

D_SUPPLIER,

D_NAME,

D_DEBITACCT,

D_DEBITAMT,

D_CREDITACCT,

D_CREDITAMT

FROM

\\sgs-bit-01\QlikView\AccessPoint\DATA\donepo.csv

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Re: How many record can be loaded from CSV file

Clever_Anjos — Fri, 06 Dec 2013 01:33:39 GMT

There´s no physical limitation but your RAM capacity.

Please check

1) If there´s empty lines into your file

2) Check using Table Viewer (Control-T) how many lines were read

using a table box can lead to wrong counting, because duplicate lines are skipped

Re: How many record can be loaded from CSV file

Fri, 06 Dec 2013 01:37:25 GMT

Dear all,

Just did some try and i realise the problem might be on the load statement.

if i change this line

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

(txt, codepage is 1252, embedded labels, delimiter is ';');

then it gives me 39k+ record. Although still less than records in original csv. Can somebody advise what is msq? is my load script correct?

Re: How many record can be loaded from CSV file

Clever_Anjos — Fri, 06 Dec 2013 01:41:09 GMT

You script seems correct, please check my previous answert

Re: How many record can be loaded from CSV file

Fri, 06 Dec 2013 02:12:11 GMT

?? table skip the duplicate line? Then what is the best way to have a listing view like database table view?

Re: How many record can be loaded from CSV file

jagan — Fri, 06 Dec 2013 02:18:40 GMT

Hi,

Try like this

Data:

LOAD

RowNo() AS RecordNumber

FROM FileName.csv;

Now add all the fields including RecordNumber in the Table box, it will show all the records, now there are no duplicates since there is unique record number for each row.

Hope this helps you.

Regards,

Jagan.

Re: How many record can be loaded from CSV file

Clever_Anjos — Fri, 06 Dec 2013 02:22:01 GMT

Jagan Mohan gave what you need to create totally distinct lines

Re: How many record can be loaded from CSV file

Fri, 06 Dec 2013 02:37:59 GMT

Hi Mohan,

Below is what i have tested

1) Based on your script. i got all the record show 41887 (all records)

2) if i use my script and add 'RowNo() AS RecordNumber' then i got 41887

3) if i use my script without 'RowNo() AS RecordNumber' then i got 36927 after export to excel

But all these result change to less if i include the msq like below. May i know what is msq?

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

And why the result different with recordnumber and without the recordnumber?

Re: How many record can be loaded from CSV file

jagan — Fri, 06 Dec 2013 02:54:38 GMT

Hi,

From Reference manual

msq(modern style quoting) indicates that the style of quotation marks is taken modern.

This only applies for files of type txt.

Regards,

Jagan.

Re: How many record can be loaded from CSV file

Fri, 06 Dec 2013 02:57:56 GMT

Ok. I will exclude msq

But may i know how come the script give me difference result if i include and exclude the below line?

include : RowNo() AS RecordNumber = ALL RECORDS SHOWN 41887

Excldue : RowNo() AS RecordNumber = ALL RECORDS SHOWN 36K+

Re: How many record can be loaded from CSV file

Clever_Anjos — Fri, 06 Dec 2013 03:03:09 GMT

Are you checking the Table Viewer? (Ctrl-T)

It´s the best way to check how many rows were loaded

Re: How many record can be loaded from CSV file

Fri, 06 Dec 2013 03:07:46 GMT

I went to to the table viewer but it doesnt show me the number of rows loaded. where can i check this? It only give me table view after i right click the table and click preview.

Re: How many record can be loaded from CSV file

Clever_Anjos — Fri, 06 Dec 2013 03:13:28 GMT

Point you mouse to tablename and wait for 2 seconds, a pop-up will show up

Re: How many record can be loaded from CSV file

jagan — Fri, 06 Dec 2013 03:18:01 GMT

Hi,

If you check Table box count it would differ with actual, because it removes duplicate rows. You check Table Viewer? (Ctrl-T)

or In script try like this

LET vNoOfRows = NoOfRows('TableName');

and check the variable value in a Text Object.

Regards,

Jagan.

Re: How many record can be loaded from CSV file

Fri, 06 Dec 2013 03:32:25 GMT

I think Anjos and Mohan are correct.

The problem also in table box as it remove certain line. I checked the table viewer. It gives the all the rows.

But question now is why table box remove certain rows. I manage to found this. We have 4 unique row. But tablebox only display the top 3 rows and exclude the last one. May i know how come it didnt show the last row in table box as you can see all 4 rows are unique

Re: How many record can be loaded from CSV file

Clever_Anjos — Mon, 09 Dec 2013 11:09:56 GMT

All those fields are into Table box?

QlikView Table Box show only distinct tuple of fields

Re: How many record can be loaded from CSV file

Tue, 10 Dec 2013 00:40:48 GMT

Yes. Those are the only fields.

Anyway, the first 3 row able to show in table box but not the last row.

Any different between the distinct and distinct tuple. I still cant figure out why the last row doesnt show

Re: How many record can be loaded from CSV file

Clever_Anjos — Tue, 10 Dec 2013 02:11:09 GMT

Is it possible you post a sample with those 4 records?