Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

agdgn123
New Contributor III

How can I limit the amount of table rows uploaded in a script?

I have an excel spreadsheet that contains 2 separate tables one under the other . They are separated by a blank row. The top table has a permanent amount of rows (4 + headers). The structure of the spreadsheet  cannot be changed.

I want to load them as 2 separate tables.

I can easily set the beginning of the dataload for the bottom table by setting the correct number in "header is x lines".

Is there a way to limit the amount of rows uploaded from the top table?

example:

Propertiesoption 1Option 2
size35
weight3030
height1.52.3
depth

5

6

Color PricesOption 1Option 2
Blue5$10$
Red6$10$
Yellow7$10$
1 Solution

Accepted Solutions
shubham_singh
Contributor II

Re: How can I limit the amount of table rows uploaded in a script?

Write "first  4" without quotes between load and Table name key word.

e.g.

ABC:

First 4

LOAD * FROM [lib://Folder/File.xlsx];

10 Replies
omarbensalem
Esteemed Contributor

Re: How can I limit the amount of table rows uploaded in a script?

Maybe like this?

load * where key<=4;

load *,RecNo() as key;

LOAD

    Properties,

    "option 1",

    "Option 2"

FROM [lib://source/Limit rows.xlsx]

(ooxml, embedded labels, table is Feuil1);

drop field key;

Capture.PNG

beck110979
Valued Contributor III

Re: How can I limit the amount of table rows uploaded in a script?

Hi Anat,

what kind of output do you expect? Give a screenshot as example?

Beck

klausskalts
Contributor

Re: How can I limit the amount of table rows uploaded in a script?

  1. Can you create named areas in the spread-sheet?
    Then this is the easiest solution. The Named areas can be referenced as tables in Qlik
  2. Will the top table always have the same number of rows?
    Then you can limit the number of rows you load by hard code

  3. Will the lenghth of the tables vary
    Then you'll have to 'find' the empty line and store that number in a script-generated-variable and use that to determine table one and two ...


beck110979
Valued Contributor III

Re: How can I limit the amount of table rows uploaded in a script?

Thanks for translation

agdgn123
New Contributor III

Re: How can I limit the amount of table rows uploaded in a script?

Hi Kaus,

1. yes - i can name ranges in the spreadsheet. how can use the named ranges in the script?

2. yes- the top table will always have the same number of rows. The bottom table will vary. Based on my answer can I limit the number of rows I load from the top table? what's the script expression for this?

Thanks!

klausskalts
Contributor

Re: How can I limit the amount of table rows uploaded in a script?

The Named ranges will appear as tables when you load the Excel sheet and is the easyest way to fix the problem:

klausskalts
Contributor

Re: How can I limit the amount of table rows uploaded in a script?

You can also fix it script-wise - below the two areas are loaded sequentially and the number of row's loaded is limited in the first load-statement and the number of header rows are adjusted in the second load-statement:

shubham_singh
Contributor II

Re: How can I limit the amount of table rows uploaded in a script?

Write "first  4" without quotes between load and Table name key word.

e.g.

ABC:

First 4

LOAD * FROM [lib://Folder/File.xlsx];

agdgn123
New Contributor III

Re: How can I limit the amount of table rows uploaded in a script?

Thanks Shubham! It worked! I never dreamed it would be so easy!!