topic Re: Sort strings based on number character in Dashboards & Visualizations

How to sort strings based on number character?

POR160893 — Tue, 03 May 2022 16:11:02 GMT

Hi,

I have a field which is a concatenation of a URL and a Sequence number, e.g. /google.ie:23 or /ebay.com:43.

I need to order this string field in descending order base based on the string number at the end of the field and then create 2 fields "To" and "From" showing:

To From
/yahoo.ie:1 /google.ie:2
/google.ie:2 /facebook.ie:3
.............................

At the moment, I am able to do the concatenation, but I am unable to sort on the numbers or create the required "To" or "From" fields:
index = .....
| eval time_epoch = strptime('SESSION_TIMESTAMP', "%Y-%m-%d %H:%M:%S")
| convert ctime(time_epoch) as hour_minute timeformat="%Y-%m-%d %H:%M"
| strcat URL_PATH ":" SEQUENCE combo_time
| table combo_time

Can you please help?

Many thanks,
P

Re: Sort strings based on number character

ITWhisperer — Tue, 03 May 2022 11:53:17 GMT

Can you not just sort by SEQUENCE?

Re: Sort strings based on number character

POR160893 — Tue, 03 May 2022 12:27:50 GMT

Hey,

Yes, I sorted on SEQUENCE. This is where I am currently at:

......
| eval time_epoch = strptime('SESSION_TIMESTAMP', "%Y-%m-%d %H:%M:%S")
| convert ctime(time_epoch) as hour_minute timeformat="%Y-%m-%d %H:%M"
| strcat URL_PATH ":" SEQUENCE combo_time
| table combo_time, SEQUENCE
| eval temp = tonumber(SEQUENCE)
| sort temp
| rename combo_time as from
| eval to = from
| table from,to

The output is as follows:

For the "to" field, I need to remove the first number and replace it with the 2nd, and continue doing this until "from" has its last number and "to" has nothing, like this:

from to
....:1 ......:2
.....2 .......:3
.....:3 .......:4

........................................
.........N <BLANK>

How can I do this?????

(I gave you Karma for your last message though 😀)

Many thanks!

Re: Sort strings based on number character

POR160893 — Tue, 03 May 2022 14:36:56 GMT