Sequence of numbers

[mato32188]

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:

ID	Group
1	1
2	1
3	1
4	2
5	2
6	2
7	3
...	...
15023	n (?5008?)

Thank you for response.

BR

M

[Miguel_Angel_Baeyens · Accepted solution]

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]

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]

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

];

[mato32188]

Thanks Miguel, Ceil() is correct function.

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

[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.