Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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;
Hi,
Try
LOAD
*,
Ceil(ID / 3) AS Group
FROM DataSource;
Hi
Try like this
LOAD *, Ceil(ID/3) as Group Inline
[
ID
1
2
3
4
5
6
7
8
];
Thanks Miguel, Ceil() is correct function.
I expected some 'for ... loop' approach in case values would be other data type.
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.