How to use values of column as column name?

[apple143]

Hello. I've come to ask again continuously the question I asked few days ago
This is my last question: https://answers.splunk.com/answers/664833/how-to-separate-rows-as-column.html

My data have 3 fields: LastName, FirstName, Age

I'm using query last 2days
| tstats count where LastName=* by FirstName, Age, _time span=1d prestats=true
| stats count by FirstName, Age, _time

then I have the result like below
FirstName--------------Age---------------_time--------------------count
A--------------------------24------------------2018-06-11-----------11
A--------------------------24------------------2018-06-12-----------22
A--------------------------30------------------2018-06-11-----------33
A--------------------------30------------------2018-06-12-----------44
B--------------------------26------------------2018-06-11-----------55
B--------------------------26------------------2018-06-12-----------66

and I want to make a result like
FirstName--------------Age---------------2daysago_count---------1dayago_count
A--------------------------24-----------------11----------------------------22
A--------------------------30-----------------33----------------------------44
B--------------------------26-----------------55----------------------------66
(Not for this question, but I'm gonna make a new column to calculate differences between 2daysago_count and 1dayago_count)

What should I do?
No matter which way you search, if I can use "tstats" (for time reduction)
You can change from the first table to second one or make a new search or whatever

Help please

• If I could do like above not only based on time space but also one of other fields, please let me know..

[FrankVl · Accepted solution]

Try this:

| tstats count where LastName=* by FirstName, Age, _time span=1d prestats=true
| stats count by FirstName, Age, _time
| eval 2daysago_count = if(_time<relative_time(now(), "-1d@d"),count,0)
| eval 1dayago_count = if(_time>=relative_time(now(), "-1d@d"),count,0)
| stats sum(1dayago_count) as 1dayago_count sum(2daysago_count) as 2daysago_count by FirstName, Age

[apple143]

WOW that's what I want!
Thank you very much.

You save my life.. Thank you!!

[renjith_nair]

Try

| tstats count where LastName=* by FirstName, Age, _time span=1d prestats=true
| stats count by FirstName, Age, _time
|stats ealiest(count) as 2daysago_count,latest(count) as 1dayago_count by FirstName, Age

[apple143]

Wow It works! Thank you.

Now I found some minor problem here,
When a count is zero, the the latest value and the earliest value are same (I mean printed the same value, cause there is only one value)
So I cannot calculate the difference between them.

How can I make it?

[renjith_nair]

Even though the count is 0, it should be printed under 2days and 1 days since its split by Firstname and age. If this is not the case, please provide some sample where you have this issue. Happy to assist

[apple143]

In my case, one person makes data for two days. So I have to count each day by person.
But, if some person(ex. Name is AA and Age is 35) had not made any event 2 days ago (1 day ago's count is 50), I get table like this

FirstName--------------Age---------------2daysago_count---------1dayago_count
AA------------------------35-----------------50----------------------------50

and I want to make the table like below
FirstName--------------Age---------------2daysago_count---------1dayago_count
AA------------------------35-----------------0------------------------------50

[renjith_nair]

@apple143, ok so where is the problem now? or are you not getting this? It would be helpful if you could say what are you getting and what you expect to get

[apple143]

My problem is that I cannot make the later one in my last comment.
I am only able to get the former one.

Actually I solved the problem searching twice.
Like

(table 1)
| where _time=strftime(time(), "%Y-%m-%d")
| stats count as today by FirstName, Age
| join type=left FirstName, Age
[ (table 1)
| where _time=strftime(time()-86400, "%Y-%m-%d")
| stats count as yesterday by FirstName, Age]

but I don't want to search twice for separate them.

Sorry to bother you 😞

[FrankVl]

See my answer below, think that does what you want.

[FrankVl]

The problem is that your solution assumes there are multiple entries for each Firstname,Age combination. 1 for each day. If one of those entries doesn't exist, your earliest and latest will give the same outcome, rather than correctly reflecting that one of the two columns should be 0.