topic load last n rows from excel in QlikView

load last n rows from excel

Mon, 05 Jul 2010 13:26:26 GMT

Hello. I hope someone can help me.I want to load the last n rows from a table in excel.How can i do that?

load last n rows from excel

fernandotoledo — Mon, 05 Jul 2010 13:45:40 GMT

You have to load all table first then, use the NoOfRows('table') Function to know how many rows there is in the table and store it in a variable, like

LET a = NoOfRows('tab1');

then load it again with a "where RowNo() > $(A-N)" statement

load last n rows from excel

Mon, 05 Jul 2010 14:14:40 GMT

Thanks for the reply.It seems that there in no way to avoid the first load.

AW:Re: load last n rows from excel

brenner_martina — Mon, 05 Jul 2010 14:31:02 GMT

Hi konstantinos,

do you know how many rows you want to load? You can use the header size with a variable and then load the titleline with the fieldnames to rename all fileds.

AW:Re: load last n rows from excel

Mon, 05 Jul 2010 14:56:19 GMT

Hi Martina,

thanks for the reply.Unfortunately i don't know the number of rows.

AW:Re: AW:Re: load last n rows from excel

brenner_martina — Mon, 05 Jul 2010 15:01:35 GMT

Hi, konstatntinos,

how do you find them out? Did you try the possibilities of Enable Transformation Step? there you can enter conditions to find the right lines to load?!

AW:Re: AW:Re: load last n rows from excel

Mon, 05 Jul 2010 15:10:19 GMT

Hi Martina,

the only condition is that the rows must be the last n in the table. I tried the Transformation step but since the number of rows is not fixed i did n't find something that will do the job on any case.

AW:Re: AW:Re: AW:Re: load last n rows from excel

brenner_martina — Mon, 05 Jul 2010 15:13:53 GMT

Hi, konstantinos,

do you know any condition how many n rows you have to load?

load last n rows from excel

Miguel_Angel_Baeyens — Mon, 05 Jul 2010 16:33:45 GMT

Hello Konstantinos,

Maybe not the most beautiful way, but loading the table frist to know the number of rows (I don't know any other way of counting them, but loading it or even -untested- connect via ODBC/OLE DB) then you can use function RecNo() to start loading when RecNo() greater than total rows - n.

Does that suit you?

AW:Re: AW:Re: AW:Re: load last n rows from excel

Mon, 05 Jul 2010 17:27:26 GMT

Hi Martina,

n is a fixed number, e.g n=5. I just need the last 5 records added to a table.

load last n rows from excel

Mon, 05 Jul 2010 17:31:56 GMT

Hello Miguel,

I guess what you propose is probably the best way to do it. Thank you very much.

load last n rows from excel

mike_garcia — Mon, 05 Jul 2010 19:42:19 GMT

Hi,

There must be some order logic for the table, for example by date, order #, invoice #, etc. So Im thinking, you can reorder the table before being read by qlikview. You can use a simple macro (in Excel) to do just that.

For instance, If you want the 10 most recent orders, you first call the macro (maybe with a vbs), save the file (to the same folder or a new one) and then read that modified file with the Prefix First. Something like

First 10 Load * from file.xls;

Just another way to accomplish the same, dont really know if it is the best.

Mike.

load last n rows from excel

Tue, 06 Jul 2010 07:44:54 GMT

Hi Mike,

Yes,the entries in the table are related to date, so your idea makes sense.Thanks for the reply.

load last n rows from excel

Miguel_Angel_Baeyens — Tue, 06 Jul 2010 07:58:05 GMT

Hello Konstantinos,

I've seen that enabling transformation step, clicking on "Rotate" tab, "Left" twice, table "turns" 180 degrees, so first rows are now last rows. Combined with Mike's suggestion of FIRST n LOAD or WHERE RecNo() <= n you get it!

Regards.

load last n rows from excel

Tue, 06 Jul 2010 08:33:50 GMT

Hello Miguel,

Yes that works!Thank you

load last n rows from excel

Miguel_Angel_Baeyens — Tue, 06 Jul 2010 08:36:20 GMT

By the way, good one Mike! Didn't know about that FIRST n LOAD

load last n rows from excel

Wed, 15 Feb 2012 08:08:24 GMT

Hi All,

very informative stuff.

Can anyone explain abt various Steps in Enable Transformation Step for excel like Garbage tab(Conditional Tab),Fill,Column,Unwrap.

Although these names are suggestive but i haven't found any good detailed tutorial.

Regards

Re: load last n rows from excel

farolito20 — Wed, 02 Jan 2013 14:25:36 GMT

if not exist something to load the last row? like : last 1 load

Re: load last n rows from excel

Sun, 03 Feb 2013 07:37:15 GMT

Good solution. Thank you sir.