topic Re: How to delete empty rows ? in QlikView

How to delete empty rows ?

Sun, 28 Sep 2014 11:55:56 GMT

How can I delete all empty rows after the last rows from excel if they do not contain any data in order to reduce the size of the file via QlikView Script?

Any suggestions will be highly appreciated?

Re: How to delete empty rows ?

ashfaq_haseeb — Sun, 28 Sep 2014 11:59:51 GMT

Can you provide sample data to demonstrate issue and the required o/p.

Regards

ASHFAQ

Re: How to delete empty rows ?

nagaiank — Sun, 28 Sep 2014 12:02:27 GMT

Do you want to know how to delete empty rows in Excel?

If your question is about deleting after load, you may try the following.

If your data has a key field which cannot be null or empty, you may filter the rows using the preceding load

LOAD * where Len(Trim([your key field])) > 0;

LOAD ..... From (Your Excel file);

Re: How to delete empty rows ?

Sun, 28 Sep 2014 12:10:40 GMT

I need to delete all rows after the last rows which contain no data and they are completely empty.

Re: How to delete empty rows ?

Sun, 28 Sep 2014 12:29:16 GMT

Hi NagaianK,

What do u mean by key field here - Len(Trim([your key field])) ?

Re: Re: How to delete empty rows ?

ashfaq_haseeb — Sun, 28 Sep 2014 12:45:41 GMT

Hi,

By default Qlik Will not load empty lines, unless you have some null values.

Try with below code.

LOAD Customer,
    [Sales Order ID],
    ShipDate,
    Product,
    Sales,
    Quantity
FROM

(biff, embedded labels, table is [Sales Orders$])
where len(Trim(Customer)>0);

Let me know if that worked..

Regards

ASHFAQ

Re: How to delete empty rows ?

Sun, 28 Sep 2014 12:55:14 GMT

where len(Trim(Customer)>0);

Can you please me the above script and why do we use this ?

On Sun, Sep 28, 2014 at 2:46 PM, Ashfaq Mohammed <qcwebmaster@qlikview.com>

Re: How to delete empty rows ?

ashfaq_haseeb — Sun, 28 Sep 2014 12:58:24 GMT

Hi,

As I Say QlikView will not load empty lines by default, provided there are some special characters available in excel.

To avoid that we use where len(Trim(Customer)>0);

Regards

ASHFAQ

Re: How to delete empty rows ?

VamsiKrishna05 — Wed, 13 Jul 2022 11:29:21 GMT

Hi Team,

Need a small help...

I dont want to display the empty cells, i would like to delete entire rows where ever the value is blank. How do i do that.

where ever the [Called Number] is blank or null i dont want to show that rows.