Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
mato32188
Specialist
Specialist

Sequence of numbers

Hi QV fans,

I would appreciate any help. Need to generate sequence of numbers based on condition.

Input data:

ID
1
2
3
...
15023

Issue: Create column 'Group' for first 3 IDs with value 1, then continue to next 3 IDs and fill column 'Group' with value 2, ... until last ID's value is reached.

Expected output:

IDGroup
11
21
31
42
52
62
73
......
15023n (?5008?)

Thank you for response.

BR

M

ECG line chart is the most important visualization in your life.
1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hi Martin,

Not having tested it, but the Ceil() or Div() functions should do it

Ceil(ID / 3) AS Group

or

Div(ID, 3) AS Group

Miguel

View solution in original post

6 Replies
Miguel_Angel_Baeyens

Hi Martin,

Not having tested it, but the Ceil() or Div() functions should do it

Ceil(ID / 3) AS Group

or

Div(ID, 3) AS Group

Miguel

MK_QSL
MVP
MVP

Temp:

Load * Inline

[

  ID

  1

  2

  3

  4

  5

  6

  7

  8

  9

];

Temp2:

Load

  ID,

  Mod(ID,3) as TempField

Resident Temp

Order By ID;

Drop Table Temp;

Final:

Load

  ID,

  If(RowNo() = 1, 1,

  If(Previous(TempField) = 1 or Previous(TempField) = 2,Peek('Group'),RangeSum(Peek('Group')+1))) as Group

Resident Temp2

Order By ID;

Drop Field TempField;

jagan
Partner - Champion III
Partner - Champion III

Hi,

Try

LOAD

*,

Ceil(ID / 3) AS Group

FROM DataSource;



MayilVahanan

Hi

Try like this

LOAD *, Ceil(ID/3) as Group Inline

[

ID

1

2

3

4

5

6

7

8

];

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
mato32188
Specialist
Specialist
Author

Thanks Miguel, Ceil() is correct function.

I expected some 'for ... loop' approach in case values would be other data type.

ECG line chart is the most important visualization in your life.
Miguel_Angel_Baeyens

Yes, a FOR loop will also work, but it will slow down your script execution time drastically.

It is better if you can avoid them like in this case.