How to compare the date from the lookup and write new value?

[zen29d]

Hello, Splunkers.

Problem Statement:
I've searched the data with "date" and "score" to get the latest data and got the result. (Date may or may not be the current time.)

index=sampledata | head 10 | table Date Score | sort -Date| head 1

Result:

Date	Score
2023-02-24	20

I have created a lookup table "score.csv" to behave like variables to store data. 

Saved_Date	Saved_Score
2023-01-15	30

Now, I want to compare something like below

| eval current_timestamp=strptime(Date, "%Y-%m-%d")
| lookup score.csv Saved_Date <Required Help >
| eval saved_timestamp=strptime(Saved_Date, "%Y-%m-%d")
| eval new=if(current_timestamp > saved_timestamp, "Yes","No")
| where new="Yes"
| <want to overwrite with "Date" and "Score" in score.csv>

[PickleRick]

As an additional hint not directly related to the main issue - it's often better to store the timestamp as a unix timestamp than a string rendition of it. It's easier to manipulate, calculate offsets and so on. You only render it to string when displaying.

[yuanliu]

Something like

index=sampledata | head 10 | table Date Score | sort -Date| head 1
| rename Date as Saved_Date, Score as Saved_Score
| append
    [| inputlookup score.csv ``` Saved_Date, Saved_Score ``` ]
| sort - Saved_Date
| head 1
| outputlookup score.csv

[zen29d]

Thanks for the help, I tried and it worked, and thought it will solve the logic however, I think, I messed up more. 
The logic goes like this.

Current_Date = Date
Current_Score = Score 
Previous_Date = Stored_Date
Previous_Score = Stored_Score

1. Read the Current_Date, Current_Score, Previous_Date, Previous_Score. (Solved)  
1. Compare the Scores: Delta = Current_Score - Previous_Score.
2. If a change is observed: 0<Delta>0, Generate an Alert.
3. If NOT, evaluate the Current_Date and Current_Score and write back the lookup.

[yuanliu]

I don't think you can use one command to both do alerting and write back with the logic you just described.  So, the two functions have to be in separate searches.

1. Write-back

index=sampledata | head 10 | table Date Score | sort -Date| head 1
| rename Date as Saved_Date, Score as Saved_Score
| append
    [| inputlookup score.csv ``` Saved_Date, Saved_Score ``` ]
| stats list(Saved_Date) as Saved_Date list(Saved_Score) as Saved_Score dc(Saved_Score) as distinct
| foreach Saved_*
    [eval <<FIELD>> = if(distinct == 1, mvindex(<<FIELD>>, 0), mvindex(<<FIELD>>, 1)]
| outputlookup score.csv

In the above, if the two scores are different, simply write back the values from lookup itself.

2. Alerting

index=sampledata | head 10 | table Date Score | sort -Date| head 1
| append
    [| inputlookup score.csv ``` Saved_Date, Saved_Score ``` ]
| stats values(Date) as Date values(Saved_Date) as Saved_Date values(Score) as Score values(Saved_Score) as Saved_Score
| eval Delta = Score - Saved_Score
| where Delta != 0

[ITWhisperer]

Not quite true - you can combine them into a single search using appendpipe - here is a runanywhere example which updates the date if the scores are the same, and returns no results, or returns results if the scores are different without updating the csv

| makeresults
| eval score=random()%10
| rename score as saved_score, _time as saved_time
| append 
    [| inputlookup score.csv]
| appendpipe
    [| stats list(saved_time) as saved_time list(saved_score) as saved_score dc(saved_score) as distinct
    | foreach saved_*
        [| eval <<FIELD>>=if(distinct=1, mvindex(<<FIELD>>,0),mvindex(<<FIELD>>,1))]
    | fields - distinct
    | outputlookup score.csv
    | where 1==2 ]
| stats values(saved_time) as time values(saved_score) as score range(saved_score) as delta
| where delta != 0