topic Remove a range of rows based on condition in QlikView

Remove a range of rows based on condition

Thu, 07 Jul 2011 16:01:33 GMT

Hi,

I have multiple, similar Excel files. When I grab the information from them, there is a header block in each, that I need to remove on the load. The problem is that the range of rows varies, BUT I have a reference point - the last row will always have the same string in the first column.

Do you know if there is a conditional range remove? Or maybe there is a work-around?

Thank you,

Siarhei K.

Remove a range of rows based on condition

Thu, 07 Jul 2011 18:19:16 GMT

You should be able to just include a where clause in your load script to eliminate your problem rows.

WHERE (first_field <> 'same_string'

or first_field <> ' ') //whatever else you may need

Remove a range of rows based on condition

Fri, 08 Jul 2011 19:03:43 GMT

Sure, but as far as I understand, this will only exclude specific rows rather than a range of rows.

Re: Remove a range of rows based on condition

johnw — Fri, 08 Jul 2011 23:35:10 GMT

So, what does this data look like? Something like this?

Blah blah blah blah
blah blah blah blah
blah blah blah blah
END blah blah blah
Ann 0123 blue 10
Bob 1234 red 15
Chad 9912 blue 50

You can't predict what the 'blah blah blah blah' looks like, and you can't predict how many lines of it there will be. All you know is that the LAST line you need to ignore has something recognizable in the first field, in my example 'END'.

For this case, one way would be like this:

MyData:
LOAD
Field1
,Field2
,Field3
,Field4
,if(previous(Field1)='END',1,peek(Keep)) as Keep
FROM your file
;
INNER JOIN (MyData)
LOAD 1 as Keep
AUTOGENERATE 1
;
DROP FIELD Keep
;

I almost never load from files, so there may be a much simpler way of ignoring header information in files that I'm simply not aware of.

Re: Remove a range of rows based on condition

Tue, 19 Mar 2013 11:36:22 GMT

Hi,

I would like to do the exact opposite.

I want to exclude all lines after a field contains a certain value.

How would I go about doing this?

Thanks in advance!

Re: Remove a range of rows based on condition

johnw — Tue, 19 Mar 2013 16:12:10 GMT

Maybe this? The rest would be the same as before.

if(Field1='END',0,if(rowno()=1,1,peek(Keep))) as Keep