Counting the rows in a table and assigning the number to a new field

[hopkinsc]

Hi everyone,

I am trying to create a new field in Qlik to be able to use it in the page node of NPrinting so it will split a table into multiple pages if the table is too big for a single page. 

So i have a table of commentary in Qlik which is by month/year/sheet. For every month/year/sheet i want to count the number of rows, then if there are 4 or less, assign the number 1 to a new field, if it has 6 rows, then the first 4 i want to assign the number 1 and the next 2 i want to assign the number 2 etc.

Can anyone help please?

I have attached a sample 

[rwunderlich · Accepted solution]

I suggest:

Ceil(AutoNumber(RecNo(), Key) / 4)

Specifically, in your sample:

NoConcatenate
Data:
LOAD
  *,
  Ceil(AutoNumber(RecNo(), Key) / 4) as NewField
;
Load *,
  Year&'-'&Month&'-'&Sheet as Key
Resident DataINLINE; 

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

[hopkinsc]

Thats great, thanks very much Rob!