Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
If(Len(Field1) < 1,1,Ceil[Field1/35)) as Field2
If you want a max Field2, just add a test for Ceil[Field/35).
If(Len(Field1) < 1,1,Ceil[Field1/35)) as Field2
If you want a max Field2, just add a test for Ceil[Field/35).