topic Re: Add line numer during load in QlikView

Add line numer during load

Mon, 24 Oct 2016 12:01:10 GMT

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.

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

Re: Add line numer during load

squeakie_pig — Mon, 24 Oct 2016 14:18:37 GMT

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.

Re: Add line numer during load

cesaraccardi — Mon, 24 Oct 2016 14:31:03 GMT

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

LOAD

RowNo() as A,

Column2,

Column3

FROM [excel_file];

Re: Add line numer during load

adamdavi3s — Mon, 24 Oct 2016 14:33:04 GMT

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.

Re: Add line numer during load

cesaraccardi — Mon, 24 Oct 2016 14:34:00 GMT

You might have to use a preceding load.

LOAD

RowNo() as A,

;

LOAD

Columns

FROM [excel_file];

Re: Add line numer during load

MarcoWedel — Mon, 24 Oct 2016 21:56:13 GMT

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

Re: Add line numer during load

Tue, 25 Oct 2016 06:38:45 GMT

Thank you very much - it works fine 🙂