Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Add line numer during load

I have an Excel sheet where I want to add a coloumn during import where the line number is showed. How do I do that?

E.g.24-10-2016-13.59.19.png

This sheets hould have 3,4,.... 13 as coloumn A

6 Replies
squeakie_pig
Contributor

Re: Add line numer during load

I think to add it as a function in the script you need to make an excel ODBC connection string (ie. instead of importing using the Table Files button).  Think this thread has instructions for that.

Re: How to get data from xlsb to QlikView

If not, add a new column to your spreadsheet and include the excel ROW() function in there.

cesaraccardi
Valued Contributor

Re: Add line numer during load

Hi

Have you tried adding the RowNo() function in the load statement?

LOAD

     RowNo() as A,

     Column2,

     Column3

FROM [excel_file];

adamdavi3s
Honored Contributor

Re: Add line numer during load

You could just use rowno() in your qlikview script.

If your rows start on row 4, just use something like  rowno()+3 as excel_row_number

This of course assumes that Qlikview loads in order which I think it does but haven't ever really tested

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

Highlighted
cesaraccardi
Valued Contributor

Re: Add line numer during load

You might have to use a preceding load.

LOAD

     RowNo() as A,

     *

;

LOAD

     Columns

FROM [excel_file];

Re: Add line numer during load

Hi,

maybe also possible:

LOAD RecNo() as LineNumber, *

FROM Your.xlsx (ooxml, embedded labels, table is Sheet1);

Please post a sample of your Excel file to test with.

hope this helps

regards

Marco

Not applicable

Re: Add line numer during load

Thank you very much - it works fine 🙂