Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
juan_escobar
Contributor

Excluding rows from an Excel file

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?

1.png

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

2.png

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

3.png

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

Thabk you very much.

Message was edited by: juan.escobar

1 Solution

Accepted Solutions
dennisnet
Valued Contributor III

Excluding rows from an Excel file

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.

11 Replies
jason_michaelid
Honored Contributor II

Excluding rows from an Excel file

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_michaelid
Honored Contributor II

Excluding rows from an Excel file

I meant INCLUDE the non-text values only.

mazacini
Contributor III

Excluding rows from an Excel file

Or could you remove the subtotals from the Excel file?

dennisnet
Valued Contributor III

Re: Excluding rows from an Excel file

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

garbage.JPG

Click Add and OK

Hope this helps.

Dennis.

juan_escobar
Contributor

Excluding rows from an Excel file

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?

juan_escobar
Contributor

Excluding rows from an Excel file

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?

dennisnet
Valued Contributor III

Re: Excluding rows from an Excel file

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.

juan_escobar
Contributor

Excluding rows from an Excel file

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!

dennisnet
Valued Contributor III

Excluding rows from an Excel file

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.

Community Browser