Assigning new value every 35 rows

shatings — Mon, 26 Jan 2026 16:26:21 GMT

Hi Qlik friends:

Is there a syntax I can use to assign a value for every 35 rows of data? For simplicity, I'm attaching an excel with a value being assigned for every 4 rows of data in Field2. I want to write a script which would transform Field1 to Field 2.

Right now, I've been doing this kind of manually, with the below:

If (([Field1]>0 AND [Field1] <=35) OR (len([Field1])<1), '1',
If (([Field1]>=36) and ([Field1] <=70), ' 2',
If (([Field1]>=71) and ([Field1] <=105), 3',
If (([Field1]>=106) and ([Field1] <=140), '4',

If (([Field1]>=141) and ([Field1] <=175), '5', 'Excess' as Field2

My problem is that values Field2 can go well above 50 in some cases and I find it daunting to write an "if" statement 100 or so times. Furthermore, there isn't really a cap to how high Field2 can go. We run quarterly/annual reports and never know how high field2 can go. Is there a script that will automatically assign value to Field2, every 35 row?

Thank you!

Re: Assigning new value every 35 rows

jwjackso — Mon, 26 Apr 2021 18:26:07 GMT

If(Len(Field1) < 1,1,Ceil[Field1/35)) as Field2

If you want a max Field2, just add a test for Ceil[Field/35).

topic Assigning new value every 35 rows in QlikView

Assigning new value every 35 rows

Re: Assigning new value every 35 rows