topic Excluding rows from an Excel file in QlikView

Excluding rows from an Excel file

Anonymous — Wed, 21 Sep 2011 20:20:11 GMT

Hello,

I have this chart in Excel, that has the columns that I want but in the first column I found some values that are purely strings (as shown in the image). Now how can avoid including those in a QV chart?

and in QV with a chart it gets like this without any filter:

But then I select something in "EMPRESA" and look:

I want to have the chart without selection as if it has selection.

Thabk you very much.

Message was edited by: juan.escobar

Excluding rows from an Excel file

Jason_Michaelides — Thu, 22 Sep 2011 00:39:28 GMT

Hi,

I'm not sure what you're asking in the second part of this post but if you want to exclude the non-text values in your Excel load then:

Load

From Excel...

Where IsNum(Field1);

Hope this helps,

Jason

Excluding rows from an Excel file

Jason_Michaelides — Thu, 22 Sep 2011 00:40:42 GMT

I meant INCLUDE the non-text values only.

Excluding rows from an Excel file

mazacini — Thu, 22 Sep 2011 07:18:45 GMT

Or could you remove the subtotals from the Excel file?

Re: Excluding rows from an Excel file

Anonymous — Thu, 22 Sep 2011 07:55:25 GMT

Hi Juan,

If all these rows start with 'Total' you can do this by adding this line to your script:

Remove(Row, RowCnd(CellValue, 1, StrCnd(start, 'Total'))

This will remove all data that starts with 'Total' in the first colom.

You can easely do this yourself by using the script wizard for table files and use Enable Transformation Step

Select the tab Garbage an go to Conditional Delete

Set Column to 1
Select "Start with"

And fill in the value you want to delete from Excel file

Click Add and OK

Hope this helps.

Dennis.

Excluding rows from an Excel file

Anonymous — Thu, 22 Sep 2011 13:49:00 GMT

Hello Dennis,

so, in order to do this that you are suggesting (that's what I want). I have to load again the table in the sccript?

Excluding rows from an Excel file

Anonymous — Thu, 22 Sep 2011 15:50:27 GMT

Hello Dennis

I'm doing what you told me. But when I reload QV tells me that another column can't be found.

Why does this happen?

Re: Excluding rows from an Excel file

Anonymous — Mon, 26 Sep 2011 09:13:12 GMT

Can you share your Excel file with me? Or a dummy file with the same lay-out?

Then I will have a look at it.

Dennis.

Excluding rows from an Excel file

Anonymous — Mon, 26 Sep 2011 13:37:36 GMT

Dennis,

here is one copy of the xls http://db.tt/6bKnby98 and the sheet that I want is called "saldos cartera".

I haven't been able to filter that.

Thank you very much!

Excluding rows from an Excel file

Anonymous — Tue, 27 Sep 2011 06:39:27 GMT

Hi Juan,

Try this:

DataExcel:

LOAD *

FROM

[Control de Cartera 2.xlsx]

(ooxml, embedded labels, table is [saldos cartera], filters(

Remove(Row, Pos(Top, 1)),

Remove(Row, RowCnd(CellValue, 1, StrCnd(start, 'Total')))

));

Let me know of this works for you ok?

Good Luck,

Dennis.

Re: Excluding rows from an Excel file

Fri, 11 Dec 2015 12:21:57 GMT

Who knows if you'll read this 4 years on Jason... but this was very useful to me, thanks a lot.

Re: Excluding rows from an Excel file

Jason_Michaelides — Fri, 11 Dec 2015 16:16:44 GMT

Pleasure Ricky