How to create new column based on an existing one and removing the first value?

[whitefang1726]

Hello, I am trying to create a new column based on an existing field values. To keep it short, here's the scenario:

From:
Orig_No
17
26
35
44
53

I should generate this result:

Orig_No New_Orig_No
17 26
26 35
35 44
44 53
53

New_Orig_No is a new column based on Orig_No field, but no success to remove the first value on the 2nd column. How can I do it? Thanks!

[niketn]

[Updated Answer] based on updated data.

This should be valid for String data as well (as far as data is sorted as per use case). Following is updated query based on updated details. Please try the run anywhere search and confirm:

| makeresults
| eval Orig_No="Ryuu;Shishi;Tenma;Kirin;Houou"
| makemv Orig_No delim=";"
| mvexpand Orig_No
| table _time Orig_No
| reverse
| streamstats last(Orig_No) as New_Orig_No current=f window=1
| reverse
| eval New_Name=Orig_No." ".New_Orig_No

---

@whitefang1726 this seems to be a candidate for streamstats command with window=1 to pick up the previous value. Since streamstats is a streaming command dependent on time series data, you would need to have data in chronological/reverse chronological order first as per your use case (reverse would be handy command for manully reversing the order.)

<yourCurrentSearch>
| streamstats last(Orig_No) as New_Orig_No current=f window=1

Please try the following run anywhere search based on sample data provided:

| makeresults
| eval Orig_No="17;26;35;44;53"
| makemv Orig_No delim=";"
| mvexpand Orig_No
| table _time Orig_No
| reverse
| streamstats last(Orig_No) as New_Orig_No current=f window=1
| reverse

Also depending on what you need to do after you have got New Numbers, you should also check out delta(difference) and accum (sum) command as an alternate to streamstats command.

[whitefang1726]

Thanks @niketn. As stated by FrankVI, my goal was to create a new column one row up compare to the original column and this command works. Thanks for the help!

| streamstats last(Orig_No) as New_Orig_No current=f window=1

[niketn]

@whitefang1726, yes streamstats with above parameters allow you to do that. There are several examples of the same on Splunk Answers. Please accept this answer if your issue is resolved!

[poete]

Hello,

here you go:

| makeresults count=5 
| eval Orig_No=random()
| eval New_Orig_No=Orig_No/100
| fields New_Orig_No

In this case, the five values of the Orig_No column are used to compute the content of New_Orig_No (division by 100 in the example).
The column Orig_No is then dropped to kee only the New_Orig_No column

[whitefang1726]

What if the 1st colum values consist of characters (not numbers)

Example:

Orig_List
Ryuu
Shishi
Tenma
Kirin
Houou

To:
Orig_List New_List
Ryuu Shishi
Shishi Tenma
Tenma Kirin
Kirin Houou
Houou

[niketn]

@whitefang1726, I have updated my answer based on details. The same approach is valid for String as well. So you can try the run anywhere example and plug in the required code as per your need.

[FrankVl]

How does that shift the new column up one row compared to the original? Because that is what @whitefang1726 seems to be looking for.