How to load top 454 rows from excel in QlikView?

[apoorvasd]

Hello Everyone,

I am trying to load only top 454 rows from excel sheet and I used the code as below

TableName:

First 454

Load *...

but seems like the code is not working. Because when I call the field "Product", it has data in it from row 465 as shown in the screenshot and I have limited the data to be loaded only until row 454.

Any idea what I can try or check to load only top 454 rows?

Thank you.

[petter · Accepted solution]

When QlikView read an Excel sheet it will not read empty rows. So what you consider row 454 when you look at the data in Excel is really a much lower row number since there are so many blank lines in your Excel sheet.

You will have to find the number of non-blank lines in your Excel and use that number instead. You can do that easily in Excel by using the formula =COUNTA(D5:D454) function to count the number of non-empty cells in your range.

[gerry_hdm]

can you the function  Rank() ?

[apoorvasd]

Hmm...okay got it!

The empty rows will be filled later, but the total number of rows will not exceed more than 454. So is there a way where I can code it in such a way that even blank rows have to be counted and load data until row 454?

Thank you.

[petter]

Are you able to change something in the spreadsheet - add column or cell values - or is that fixed?

If you are able to get the spreadsheet changed you just have to fill in an extra column that has a value for all the rows.

[apoorvasd]

Hi Peter,

For now number of non empty rows may be say, 100. But later when data is entered in the excel sheet the number of non empty rows will increase. So I cannot say what will be the exact number of non empty rows! And I am loading multiple tabs in the excel sheet with same number of rows.

Just a quick question, is it okay if just one column has data for all rows (in all 454 rows)? Can I apply condition on that to get total number of rows to be loaded?

Thank you.

[petter]

Just add a hidden column called ProductRow and fill all the ProductRow cells with a period in each of the sheets in the workbook.

Then you can have a simple script like this:

LOAD
    Product,
    ......
FROM
  [ProductData.xlsx] (ooxml, embedded labels, table is Sheet1)
WHERE
  ProductRow = '.' AND Not(IsNull(Product)) AND Len(Trim(Product))>0;

[apoorvasd]

Hi Petter,

Thanks a lot for your response.

As you mentioned in your first response, I counted non-empty rows based on a column which had entries in all the rows. So, the actual count reduced to 450 from 454. Now the code works and I'm getting desired output.

Thanks again

[apoorvasd]

Hi Gerold,

Thanks for your response too!