topic Re: Excel Load remove row in QlikView

Excel Load remove row

Mon, 05 Aug 2013 09:41:53 GMT

I have an excel file whiche has some values at the and which are not needed. I know i can remove the row by loading it with "Remove(Row, Pos(Top, 35)),".

But is it also posible to say i want to remove all starting by the row with the value "Signature" (unknowk position). That he finds the correct row and then removes everything after this row?

Re: Excel Load remove row

tresB — Mon, 05 Aug 2013 09:53:38 GMT

Say, you have the first column col1 , then you can try something like this:

Load

Col1, Col2

From <your excel file path> Where Col1<>'Signature';

Re: Excel Load remove row

Mon, 05 Aug 2013 09:59:14 GMT

The file is like that

col1 col2

1 550

2 298

3 510

4 160

5 560

Signatur

--------

Timestamp

BlaBla

moreBlabla

now i would like to find the place where Signatur is because i would like to remove or do not load it after that beacuse it shouldnt be in the qv. The problem is, its alot after Signatur and every table has some other words in blabla so its also not easy to load then from multiple tables

Re: Excel Load remove row

Mon, 05 Aug 2013 10:00:28 GMT

plz attach your excel file

Re: Excel Load remove row

antoinelaviron — Mon, 05 Aug 2013 10:02:36 GMT

When loading your Excel file, use the wizard of Qlikview.

In the second step, tab Garbage, click on Conditional delete.

There you can add filters and you will have a staement like this (in this case we filter on the first column)

Remove(Row, RowCnd(CellValue,1,StrCnd(equal,'Signature')))

Best

Antoine

Re: Excel Load remove row

tresB — Mon, 05 Aug 2013 10:06:43 GMT

In that case, try something like this:

Load

RecNo() as Rec

From .... Where Col1='Signature';

Let vVariable=Peek('Rec',0); // edit

FinalTable:

Load Col1, Col2

from .... Where Col1 RecNo()< $(vVariable);

Sorry, a bit of change.