topic keep add RowNo() to next table in QlikView

keep add RowNo() to next table

Mon, 06 Jun 2011 11:07:54 GMT

Hi,

I have 3 excel worksheets. I have added additional field during load "RowNo()" for each worksheets and storing into QVD files.

Table1:

Load

RowNo() as Rows,

Field1,

Field2

from table1.csv;

store Table1 into table1.dvd;

drop table Table1;

Table2:

Load

RowNo() as Rows,

Field1,

Field2

from table2.csv;

store Table2 into table2.dvd;

drop table Table2;

Table3:

Load

RowNo() as Rows,

Field1,

Field2

from table3.csv;

store Table3 into table3.dvd;

drop table Table3;

Q: how can i keep continue adding RowNo to next table. Ex. if Table 1 has 500 rows than Table 2 should generate RowNo: 501 during Table2 load?

Thanks in advance!

keep add RowNo() to next table

martinpohl — Mon, 06 Jun 2011 11:16:36 GMT

insert after the store line:

let rows=peek('Rows',-1,'Table1');

Cahnge at Table2:

Rowno()+$(rows) as Rows,

and reply the command

let rows with the value of Table2

Regards

keep add RowNo() to next table

Mon, 06 Jun 2011 11:29:09 GMT

Thanks for you reply, but if we have more than 10 tables then it might take a while copy/paste to all tables. Is there anyway to do it by for each loop?

Re: keep add RowNo() to next table

chriscammers — Mon, 06 Jun 2011 11:45:01 GMT

You can follow the same approach that MartinPohl describes and use the example found in the "For Each" section of the Help files. I am going to paste it in but it is WAY more complicated than you need but you should be able to figure out how you'd like to change it to fit your situation

sub DoDir (Root)

for each Ext in 'qvw', 'qva', 'qvo', 'qvs'

for each File in filelist (Root&' \*.' &Ext)

Load '$(File)' as Name, FileSize( '$(File)' ) as

Size, FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

next Ext

for each Dir in dirlist (Root&' \*' )

call DoDir (Dir)

next Dir

end sub

call DoDir ('C:')

Best of Luck

Chris

Re: keep add RowNo() to next table

Mon, 06 Jun 2011 11:55:18 GMT

Thanks for the script. It looks complicated for me to figure out. Does anyone other thought on my initial query?