topic Re: How to load top 454 rows from excel in QlikView? in QlikView

How to load top 454 rows from excel in QlikView?

apoorvasd — Fri, 19 Jan 2018 07:06:33 GMT

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.

Re: How to load top 454 rows from excel in QlikView?

petter — Fri, 19 Jan 2018 07:33:00 GMT

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.

Re: How to load top 454 rows from excel in QlikView?

gerry_hdm — Fri, 19 Jan 2018 07:36:57 GMT

can you the function Rank() ?

Re: How to load top 454 rows from excel in QlikView?

apoorvasd — Fri, 19 Jan 2018 08:34:19 GMT

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.

Re: How to load top 454 rows from excel in QlikView?

petter — Fri, 19 Jan 2018 08:45:29 GMT

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.

Re: How to load top 454 rows from excel in QlikView?

apoorvasd — Fri, 19 Jan 2018 08:52:55 GMT

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.

Re: How to load top 454 rows from excel in QlikView?

petter — Fri, 19 Jan 2018 09:17:47 GMT

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;

Re: How to load top 454 rows from excel in QlikView?

apoorvasd — Fri, 19 Jan 2018 11:05:46 GMT

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

Re: How to load top 454 rows from excel in QlikView?

apoorvasd — Fri, 19 Jan 2018 11:06:35 GMT

Hi Gerold,

Thanks for your response too!