topic Re: RE:Load Only in QlikView

RE:Load Only

Chanty4u — Tue, 01 Mar 2016 09:43:17 GMT

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

Re: RE:Load Only

tamilarasu — Tue, 01 Mar 2016 09:48:29 GMT

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

MayilVahanan — Tue, 01 Mar 2016 09:56:50 GMT

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

avinashelite — Tue, 01 Mar 2016 10:01:09 GMT

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

Anonymous — Tue, 01 Mar 2016 10:02:05 GMT

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

sunny_talwar — Tue, 01 Mar 2016 10:02:07 GMT

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)))

));

Re: RE:Load Only

Chanty4u — Tue, 01 Mar 2016 10:06:24 GMT

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)))

));

Re: RE:Load Only

Chanty4u — Tue, 01 Mar 2016 10:06:44 GMT

Thnks all for ur inputs on this.

Re: RE:Load Only

Chanty4u — Tue, 01 Mar 2016 10:11:04 GMT

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

Re: RE:Load Only

avinashelite — Tue, 01 Mar 2016 10:14:54 GMT

Try like this

Sample 0.10 Select * from Longtable;

Re: RE:Load Only

Anonymous — Tue, 01 Mar 2016 10:18:13 GMT

If you use the load wizard for an excel file it will guide you through auto creating this script. Also clicking on the Help button in the wizard will help you.

Re: RE:Load Only

MayilVahanan — Tue, 01 Mar 2016 10:22:23 GMT

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.

Re: RE:Load Only

tresB — Tue, 01 Mar 2016 10:24:38 GMT

Or a customized script like:

FOR i=0 to 10

LOAD EmpID,

[Last Name],

[First Name],

Title,

[Hire Date],

Office,

Extension,

[Reports To],

[Year Salary]

FROM

(biff, embedded labels, table is Employee$) While RowNo()=Floor(Rand()*10*$(i));

Next i;

Re: RE:Load Only

Chanty4u — Tue, 01 Mar 2016 10:34:10 GMT

thanks for diffrnt way tresesco.

Re: RE:Load Only

Anonymous — Tue, 01 Mar 2016 10:46:17 GMT

based on the id we can give the id <=10 than we get the out put

Re: RE:Load Only

Chanty4u — Tue, 01 Mar 2016 10:47:48 GMT

thnkq sudhakar.