Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Partner - Creator II
Partner - Creator II

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

e.g.

ABC:

First 4

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

View solution in original post

10 Replies
OmarBenSalem

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

beck_bakytbek
Master
Master

Hi Anat,

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

Beck

klausskalts
Partner - Creator
Partner - Creator

  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 ...


beck_bakytbek
Master
Master

Thanks for translation

Anonymous
Not applicable
Author

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
Partner - Creator
Partner - Creator

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

klausskalts
Partner - Creator
Partner - Creator

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
Partner - Creator II
Partner - Creator II

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

e.g.

ABC:

First 4

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

Anonymous
Not applicable
Author

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