Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Qlikview not importing all records from excel

I am trying to import into excel all records from excel file;  How I can import all records into qlikview; please advise;

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD Account,

          Date,

          Credit

FROM (biff, embedded labels, table is [Transaction Detail by Account$]);

The result is as follows:

Books2000.0017/05/2012
Books2750.0031/05/2012
Books2800.0024/05/2012
Books2800.0025/05/2012
Books2800.0031/05/2012
Books2850.0023/05/2012
Books2850.0029/05/2012
Books2850.0030/05/2012
Books2850.0031/05/2012
Books2950.0028/05/2012
Books2950.0030/05/2012
Books2950.0031/05/2012
Books3000.0023/05/2012
Books3000.0025/05/2012
Books3050.0031/05/2012
Books3800.0030/05/2012
Books3870.0025/05/2012

Please find attached excel file;

qlikview version 8.5 excel file: office 2010

Tags (3)
1 Solution

Accepted Solutions
Employee
Employee

Re: Qlikview not importing all records from excel

When I load your Excel file, I can see that you have 41 records in it, and that is also what QlikView has loaded.

Image1.png

But in a table box, QlikView will just display the 28 unique records, i.e. it will not show duplicates. But when you use the data in calculations (pivot table, bar chart, etc.), the calculation will be made on all 41 records.

If you create a field

     RecNo() as RecordID

you will clearly see all records.

HIC

2 Replies

Re: Qlikview not importing all records from excel

Data are all loaded but equal rows are excluded in tables ... to verify create a graph with date and account as dimensions and sum(credit) as expression; you will see that totals are correct and equal to excel

Employee
Employee

Re: Qlikview not importing all records from excel

When I load your Excel file, I can see that you have 41 records in it, and that is also what QlikView has loaded.

Image1.png

But in a table box, QlikView will just display the 28 unique records, i.e. it will not show duplicates. But when you use the data in calculations (pivot table, bar chart, etc.), the calculation will be made on all 41 records.

If you create a field

     RecNo() as RecordID

you will clearly see all records.

HIC

Community Browser