Greetings,
I am trying to create a panel that helps me track expired trainings. What I am trying to do is to take the Class_Date and add 1year to it then compare that to todays date. So, IF Class_Date + 1 year > todays date, "Expired", "Valid".
This is what I have so far:
| inputlookup Classes.csv
| eval timenow = strftime(now(), "%Y-%m-%d")
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
| eval ConvertedClassDate = ConvertedClassDate + 31536000)
| eval ConvertedTodaysDate = strptime(timenow, "%Y-%m-%d")
| eval Status = IF((ConvertedTodays_Date < ConvertedClassDate), "Expired", "Valid")
| table Username, Class_Date, Status
All help is greatly appreciated!
now() is already in epoch so no need to convert...simply you can compare like
| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
so you can try this:
| inputlookup Classes.csv
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
| eval ConvertedClassDate = ConvertedClassDate + 31536000)
| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
| table Username, Class_Date, Status
Here Class_Date is expected in Year-month-day format like 2018-02-15
I'm not sure where the above effort stood, but this seems to work, and is based from the same makeresults set:
| makeresults
| eval Class_Date="2018-1-1"
| append [| makeresults | eval Class_Date="2018-12-12"]
| append [| makeresults | eval Class_Date="2017-1-2"]
| append [| makeresults | eval Class_Date="2017-1-3"]
| eval class_plus_one_year = relative_time(strptime(Class_Date, "%Y-%m-%d"), "+1y")
| eval Status = if(now() > class_plus_one_year, "Expired", "Valid")
now() is already in epoch so no need to convert...simply you can compare like
| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
so you can try this:
| inputlookup Classes.csv
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
| eval ConvertedClassDate = ConvertedClassDate + 31536000)
| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
| table Username, Class_Date, Status
Here Class_Date is expected in Year-month-day format like 2018-02-15
You may consider using relative_time
instead of adding a year's worth of seconds:
| eval ConvertedClassDate = relative_time(ConvertedClassDate, "+1y")
That might be a good option too!
EDIT:
Changed it to that and it also works.
Thanks!
Hello,
I am almost there. I can see the valid and expired ones. However, this is happening:
username1 2018-1-1 Valid
username3 2018-12-12 Valid
username4 2017-1-2 Expired
username5 2017-1-3 Expired
The first entry should be expired since today's date is 2018-02-15. Why is the code making the comparison using the year?
When I tried I get exact reverse output ...check what sign you are using >
or <
in eval expression:
Try this run anywhere search:
|makeresults|eval Class_Date="2018-1-1"
|append[|makeresults|eval Class_Date="2018-12-12"]
|append[|makeresults|eval Class_Date="2017-1-2"]
|append[|makeresults|eval Class_Date="2017-1-3"]
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
| eval ConvertedClassDate = ConvertedClassDate + 31536000 |eval current=now()| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
The issue I see is that 2018-12-12 is not expired and it is marking it as expired. If I change the comparison character, it will say 2018-01-01 is valid when in reality it is not. I feel like it is making the comparison using the year and not the whole date.
Username ClassDate Status
username1 2018-1-1 Valid
username2 2018-1-1 Valid
These two lines should be Expired.
I tried yours and it also does the same thing.
If I tried for date 2018-1-1 it is coming as expired only as expected ...Not sure why are receiving it as valid...could you please provide your query and also try below query :
|makeresults|eval Class_Date="2018-1-1"
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
| eval ConvertedClassDate = ConvertedClassDate + 31536000 |eval current=now()| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
Here's my code:
| inputlookup Training.csv
| eval ConvertedClassDate = strptime(ClassDate, "%Y-%m-%d")
| eval ConvertedClassDate = (ConvertedClassDate + 31536000)
| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
| table Username, ClassDate, Status
I changed the date your code so you would see what is going on:
|makeresults|eval Class_Date="2018-12-12"
| eval ConvertedClassDate = strptime(Class_Date, "%Y-%m-%d")
| eval ConvertedClassDate = ConvertedClassDate + 31536000 |eval current=now()| eval Status = IF((now() < ConvertedClassDate), "Expired", "Valid")
here logic is that :
if (Classdate + 1year ) is less than today's date then it is expired else Valid...
if it is correct logic then
for classdate=2018-12-12 +1year=2019-12-12 is not less than todays date so it will be Valid....right?
Wow. I guess I need to take a break. You have been right the whole time. I will drink a few beers on your name today.
🙂 ha ha ..
try if(now() > class_plus_one_year, "Expired", "Valid")