Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Chanty4u
Esteemed Contributor III

RE:Load Only

Hi all ,

I have one excel data.  with in that  i want to load only  last 10records of data only?

how can i load dis data in qvw?

Thanks in advance

Suresh

1 Solution

Accepted Solutions

Re: RE:Load Only

Hi

Try like this

Sample 0.25 LOAD EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary],

     RowNo() AS Row

FROM

EmpOff.xls

(biff, embedded labels, table is Employee$);

The sample prefix to a Load or Select (SQL) statement is used for loading a random sample of records from the data source.

15 Replies

Re: RE:Load Only

Hi Suresh,

Table:

LOAD EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary],

     RecNo() as RecNo

FROM

[..\Desktop\EmpOff.xls]

(biff, embedded labels, table is Employee$);

LET a = Peek('RecNo');

NoConcatenate

Final:

Load*

Resident Table Where RecNo>= $(a) -9;

DROP Table Table;

Re: RE:Load Only

Hi

Try like this

Temp:

LOAD EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary],

     RowNo() AS Row

FROM

EmpOff.xls

(biff, embedded labels, table is Employee$);

NoConcatenate

First 10 LOAD * Resident Temp order by Row desc;

DROP Table Temp;

Re: RE:Load Only

You can also try like ...

sort your excel file in descending order and then use the

first 10 function

hope this might also help you

Re: RE:Load Only

Or maybe like this

LOAD EmpID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary]

FROM

EmpOff.xls

(biff, embedded labels, table is Employee$, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 11), Select(1, 0)))

));

This should be efficient as it only loads the last 10 rows, as opposed to loading all rows and then discarding the unwanted rows.

Re: RE:Load Only

Try this:

Table:

Directory;

LOAD EmpID,

    [Last Name],

    [First Name],

    Title,

    [Hire Date],

    Office,

    Extension,

    [Reports To],

    [Year Salary]

FROM

EmpOff.xls

(biff, embedded labels, table is Employee$, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 11), Select(1, 0)))

));

Chanty4u
Esteemed Contributor III

Re: RE:Load Only

thnks sunny,

can u explain how this works?what it exctly does?

filters(

Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 11), Select(1, 0)))

));

Chanty4u
Esteemed Contributor III

Re: RE:Load Only

Thnks all for ur inputs on this.

Chanty4u
Esteemed Contributor III

Re: RE:Load Only

if i want to load random data of 10 records only how can do that?

Re: RE:Load Only

Try like this

Sample 0.10 Select * from Longtable;