topic Re: Append a rownumber to a table quickly in qlikview in QlikView

Append a rownumber to a table quickly in qlikview

SRT10 — Sat, 30 Oct 2021 11:40:45 GMT

Have a data of about ~85,177,324 records in a qlik memory table. I need to assign a rownumber to each record. How can i do it quickly. The above data set is concatenated into a table from various qvds and now I need to have a rownumber to each record entry. Rowno() is taking lot of time to do this and exract takes about one hour to get loaded.

Is there any optimised way to do this..

Data as of now..

Name	Address	pincode
abc	aaa	123
xyz	sss	3432
pqr	fff	4324
.	.	.
.	.	.
.	.	.

Output

Rowno	Name	Address	pincode
1	abc	aaa	123
2	xyz	sss	3432
3	pqr	fff	4324
.	.	.	.
.	.	.	.
85,177,324	.	.	.

If i am using rowno() while reading data from each qvd then it is making load unoptimised. Is there any optimised way to do this considering large data set

Re: Append a rownumber to a table quickly in qlikview

Lisa_P — Sun, 31 Oct 2021 21:59:55 GMT

Load from the QVD, but add a preceding load to it or a resident load to add in the rowno.

eg..

Load Rowno(), *;

Load Name, Address..... ;

Re: Append a rownumber to a table quickly in qlikview

SRT10 — Mon, 01 Nov 2021 05:23:37 GMT

Thanks Lisa.. this is working but this is taking time to load data. I need something which can be quickly achieved. Not sure if I am asking for more here. Is this achievable

Re: Append a rownumber to a table quickly in qlikview

SRT10 — Mon, 01 Nov 2021 06:32:19 GMT

@rwunderlich @swuehl @sunny_talwar if you could advise something here.

Re: Append a rownumber to a table quickly in qlikview

marcus_sommer — Mon, 01 Nov 2021 12:15:06 GMT

If you have an unique key within the data you may use it within an autonumber script-statement (if the origin key-information are further needed you would need to duplicate the field in beforehand). If not you could apply a logic like Lisa suggested whereby I could imagine that a resident-load may be more performant as a preceeding-approach. In general you couldn't get the rowno-id without any costs ...

A partly alternatively might be to include recno() and a source-information within the qvd's to be able to track each record from any source.

Beside this are you sure that you really need this kind of information within your application? Because such a measure could increase the needed RAM and storage space enormously and therefore it needs very good reasons to implement such information. Personally I have nowhere these information within a final application - during the development they are very helpful to validate the data but after that they have usually more disadvantages than benefits.

- Marcus

Re: Append a rownumber to a table quickly in qlikview

rwunderlich — Mon, 01 Nov 2021 17:02:54 GMT

I think simplest is best here. Stick with the optimized load and add the RecId in a simple resident load like this:

data2:
NoConcatenate
LOAD *, RecNo() as RecId Resident data;
Drop Table data;
Rename table data2 to data;

In my testing I could do about 2M rows per second. If you had similar results, it would only add about 45 seconds to your load.

BTW, it's very important to use RecNo() rather than RowNo() here. RecNo() will be 3-4 times faster than RowNo() in this case.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Append a rownumber to a table quickly in qlikview

SRT10 — Mon, 01 Nov 2021 19:14:37 GMT

Tried this but this is loading only thousands of records per second which is causing this to be very very slow. Is this because there are many columns in source table ?
Why is this happening with us. What could be the issue here. Code is almost similar to your one. It took 12 min to load data to a table using recno()

Re: Append a rownumber to a table quickly in qlikview

rwunderlich — Tue, 02 Nov 2021 03:45:29 GMT

Can you post the portion of your script where you are assigning the RecNo()?

-Rob

Re: Append a rownumber to a table quickly in qlikview

SRT10 — Tue, 02 Nov 2021 06:51:13 GMT

This is how it looks..

Data:
LOAD $(GenColList) // This has 99 columns
FROM $(vFile_Path)$(vFile_Name) (qvd);

Data2:
NoConcatenate
LOAD *,RecNo() AS Sequence Resident Data;
Drop Table Data;
Rename table Data2 to Data;

STORE Data INTO D:\Qlikview\Output\Data.QVD (QVD);

DROP TABLE Data;

Call RecordLoadTime('Data');

Re: Append a rownumber to a table quickly in qlikview

marcus_sommer — Tue, 02 Nov 2021 14:01:11 GMT

It looks now that you are loading from a single qvd - if so you could include the recno/rowno already within the creation of this file. In regard to the load-performance take a look on the available resources of the RAM because if there happens any swapping to the virtual RAM the performance will decrease significantly.

- Marcus

Re: Append a rownumber to a table quickly in qlikview

SRT10 — Tue, 02 Nov 2021 14:58:16 GMT

I can't do this while creating the qvd.. reason being there ar various qvd from various source and I will be reading data accordingly and then appending rowno or recno. so it has to be here so that number can be in sequence for each different qvd..

Ex - 1 to 100 for qvd 1

101 to 200 for qvd 2 and so on.

Can't do this at the creation of qvds as we won't be sure how many records will be there in 1st and 2nd file.

So plan is to have rec no or row no during this stage where we are aggregating all the 5 qvds

Re: Append a rownumber to a table quickly in qlikview

marcus_sommer — Tue, 02 Nov 2021 15:38:27 GMT

Especially in this case won't your intended approach lead you to a sensible tracking of the records. You will get a sequentially number but you couldn't identify with it where it comes from - at least not if you had only this information.

This means you need more and other information like some hard-coded: 'xyz' as SOURCE and/or filebasename() as FILE to track the file-name and/or subfield(filename(), '\', -2) as FOLDER to fetch more hierarchically details and/or a recno/rowno - everything what you might need should be done by creating the qvd's.

- Marcus

Re: Append a rownumber to a table quickly in qlikview

SRT10 — Tue, 02 Nov 2021 18:16:02 GMT

Source detail information is already mentioned in each respective qvd. So we can track that way to identify from were the data is coming.

++ @marcus_sommer

@rwunderlich Could you please post your code which is loading million of records in a second. Am I missing something here.

My Code..

Data2:
NoConcatenate
LOAD *,RecNo() AS Sequence Resident Data;
Drop Table Data;
Rename table Data2 to Data;

STORE Data INTO D:\Qlikview\Output\Data.QVD (QVD);

DROP TABLE Data;

Call RecordLoadTime('Data');

This loads only thousands of records per second.. 12 min for each block, at the end entire extract is taking approx. 60 min to load data from 5 different sources

Honestly it will be a great help for us 🙂

Re: Append a rownumber to a table quickly in qlikview

rwunderlich — Tue, 02 Nov 2021 20:23:18 GMT

Here is my test code for 10M rows.

data:
LOAD
'A' & div(RecNo(),2) as A
,'A' & div(RecNo(),5) as B
,'A' & div(RecNo(),10) as C
,'A' & div(RecNo(),100) as D
,'A' & div(RecNo(),500) as E
,'A' & div(RecNo(),1000) as F
AutoGenerate 1e7
;
Store data into data.qvd (qvd);
Drop Table data;
data:
Load * From data.qvd (qvd);

data2:
NoConcatenate
LOAD *, RecNo() as RecId Resident data;
Drop Table data;
Rename table data2 to data;

On a 4 core 32GB laptop here's the timing.

1st Load: 9 seconds
2nd Load: 2 seconds
3rd Load (recno): 5 seconds

I'm curious to hear results for this same script on your system.

-Rob

Re: Append a rownumber to a table quickly in qlikview

marcus_sommer — Wed, 03 Nov 2021 11:12:13 GMT

If you could track the records what is the purpose of having another record-id?

Beside this I could confirm the load-times from Rob's example within a similar environment of 4 cores and 16 GB RAM whereby each statement took 1 second more (only one trial without looking on other processes/resources).

- Marcus

Re: Append a rownumber to a table quickly in qlikview

cwolf — Thu, 04 Nov 2021 11:41:44 GMT

For million of rows and a lot of columns a resident load ist not every time a good option, because it needs much more memory.

Let's have two QVDs, each has 48.7M rows and 157 columns.

Example 1:

A: Load * from "QVD 1.qvd" (qvd); Concatenate(A) Load * from "QVD 2.qvd" (qvd); B: load RecNo() as RowNo, * Resident A; drop table A;

Runtime: 8:33
Peak working set: 44.7GB

Example 2:

A: Load RecNo() as RowNo, * from "QVD 1.qvd" (qvd); let nor=NoOfRows('A'); Concatenate(A) Load $(nor)+RecNo() as RowNo, * from "QVD 2.qvd" (qvd);

Runtime: 6:44
Peak working set: 26.4GB

So it depends on how much memory is available and how fast is the memory.

Re: Append a rownumber to a table quickly in qlikview

SRT10 — Sat, 06 Nov 2021 07:53:43 GMT

Hey

Scenario 1:

I tried your code in new application and it got completed within few seconds. This was awesome..
But when I replicate similar pattern of code in my application it was time consuming.

What can be the issue ?

Scenario 2:
have also observed that if I am reading my final qvd in your code then it is working fine for me. but in my application it is not working in optimized fashion.
😞

Scenario 3:

If I create new tab and add your code in new tab and read data from my final qvd instead of data qvd then again it is taking time to append recno

Server Configuration :

20 Core , 512 Gb Ram

@rwunderlich

Re: Append a rownumber to a table quickly in qlikview

marcus_sommer — Sat, 06 Nov 2021 09:47:09 GMT

Only if you load a single table within a script you will load THIS table. If there are more loadings the loads may effect on each other.

This is related to the way how the data are stored - each field with its distinct values is stored within an own system-table and a data-table with a bit-stuffed pointer to the system-tables. This means each further load will check if a field already exists and if it runs through this table to check if the value already exists and if not it makes a new entry there and adds the pointer to the data-table, too but if the value exists it takes the existing pointer-value and adds it to the data-table.

Therefore it's not meaningless in which order the loads are executed. Within the most scenarios it won't be relevant and not be noticed but if you hit any timely or resource-depended threshold it may be worth to look on the matter from this point of view.

Further related to the above mentioned data-handling are also measures like loading distinct or setting null-variables or system-variables like verbatim or similar things which may force a processing of the data.

Therefore I suggest to review your code in this way and also if it could be divided into multiple tasks/applications. Of course the last measure increased the overhead but the smaller tasks could be easier developed/maintained and they could in general run in parallel and/or within other time-frames.

Beside this all I recommend again to consider if this recno is really expedient - a quite good explanation to what it leads could you find here:

The Importance Of Being Distinct - Qlik Community - 1466796

which means you should have a good purpose to add such fields to your data.

- Marcus

Re: Append a rownumber to a table quickly in qlikview

rwunderlich — Sun, 07 Nov 2021 19:11:07 GMT

This is very interesting. My first guess is that your application is using a lot of RAM during the reload and you are paging during the resident load. Although your server has a lot of RAM, can you observe how much the load is using either by watching the task manager or checking the logs? I'm guessing that server is also used for QVS (Qlikview users). What is the total memory usage on the server like?

Can you post a screenshot of the data model in your app?

-Rob

Re: Append a rownumber to a table quickly in qlikview

SRT10 — Wed, 10 Nov 2021 19:17:14 GMT

Server is dedicated to Qlikview reload tasks/QMC. There are many extracts which runs on daily basis.

Usage is at bit higher side.

I have managed to use your code and it has worked for me.
What I have done -
In my extract I have loaded data from various sources in optimised format without generating RecNo() as RecId column.

There was a comment by @marcus_sommer where he said that "Only if you load a single table within a script you will load THIS table. If there are more loadings the loads may effect on each other."

Further while refreshing dashboard which is following the above one. Performed a binary load and then have added block of code suggested by you and derived RecId column as advised by you.

This block runs only once each time while reloading dashboard. Its pretty quick and gets completed in few mins not in seconds though.

One more question, are there any chance to miss any record if we are using RecNo() instead of RowNo(). I observed that RowNo() is very slow..

Does RecId guarantees to return sequential integers .

My Code -

Binary [Report.qvw];

Data2:
NoConcatenate
LOAD *, RecNo() as Sequence Resident Data; // Data table is from binary load
Drop Table Data2;
Rename table Data2 to Data;

What do you think @rwunderlich