Excluding rows from an Excel file

[Anonymous]

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

[Jason_Michaelides]

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

[Jason_Michaelides]

I meant INCLUDE the non-text values only.

[mazacini]

Or could you remove the subtotals from the Excel file?

[Anonymous]

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.

[Anonymous]

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?

[Anonymous]

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?

[Anonymous]

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.

[Anonymous]

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!

[Anonymous · Accepted solution]

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.