Adding a column

[anupgupta12]

Hi There!

I need to add a new column to below data set, Please note below data has counting from 1 to 4 then repeating but my data set keep varying, next month i may have 1 to 5 then repeating.

Data
1
2
3
4
1
2
3
4
1
2
3
4

Expected outcome.

Data	Name
1	Data set 1
2	Data set 1
3	Data set 1
4	Data set 1
1	Data set 2
2	Data set 2
3	Data set 2
4	Data set 2
1	Data set 3
2	Data set 3
3	Data set 3
4	Data set 3

[apathetichell]

Hi,

So basically add a recordid. subtract 1 from the record id and use floor division. This month it's floor division by 4. add 1 to the result. Voila. Next month - use 5. I can add an interface to allow for a user to input how many entries should be in each interval if you want.

[apathetichell]

app version...

[anupgupta12]

Thank you!!

So in  may data set maximum value is "4" so you divided it by "4", how we can select maximum value from column "data" . Is there any formula MAX(data) which will pick largest value in data

[apathetichell · Accepted solution]

Totally. Here -see the attached. What I do is I use a summarize to find the max value and then use that as the number to make groups.

[apathetichell · Accepted solution]

Also - this uses a very similar strategy to what @ Qiu employed in your other post.

So for him - he was trying to find a numbers place in a series of 1-15. We know the first group has to be 1 and we know there are 15 entries per group. If you mod divided by 15 you'll get each number's modulo of 15. The problem is at number 15. The value there is zero. And that's also a problem because you want it in the first group - so you have to subtract 1. Also since the first group isn't group zero - you add 1 overall.

That's how the floor division on dataset works too. You have to subtract one so you are getting groups of four - but the fourth entry is the last of the first group, not the first of the next group ie. floor(4/4) is 1.  So you have to subtract one from the numerator and then - since the first group is group 1, not group zero - add 1 to everything in that group.

[anupgupta12]

Thank you so much.

Can you please help me with below as well.

a data set has 15000 records and I need to add a column "SN" consisting of  1 to1000 records then again repeating from 1 to 1000, 

Data set

Data
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Expectation

Data	SN
1	1
2	2
3	4
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	1
12	2
13	3
14	4
15	5
16	6
17	7
18	18
19	19
20	20

[apathetichell · Accepted solution]

So would the SN at 1001 would be 1?

if so it's

mod(([Data]-1),1000)+1

in formula

[anupgupta12 · Accepted solution]

Problem resolved, really appreciate that. 

May I know your name?

[apathetichell]

good to hear! I'm Michael.