Comparisons

[renuka]

If suppose i have two Phases with first and last date
Phase 1=1 JAN 2020, 1 March 2020

Phase2=1Apr 2020,1jun 2020

if i get execution date as 3 Feb 2020 then my verified  Column should be displayed as Phase 1 otherwise Phase 2
 Example:

id	verified	execution Date
1	Phase1	3-Feb-20
2	Phase1	4-Feb-20
3	Phase1	5-Feb-20

[thambisetty · Accepted solution]

As I mentioned earlier, for date comparisons you have to convert them into epoch.

make sure your time formats are in proper format. for example month you mentioned sometime full month name, sometimes abbreviated month, difficult to write query if that is case, hence I have modified them in my below query.

|makeresults 
| eval test="Phase 1=1 Jan 2020,1 Mar 2020|Phase 2=1 Apr 2020,1 Jun 2020"
| makemv test delim="|"
| mvexpand test
| rex field=test "(?<phase>[^\=]+)\=(?<first_date>[^\,]+)\,(?<last_date>.*)"
| eval verified_date="3 Feb 2020"
| eval first_date_epoch=strptime(first_date,"%d %b %Y"),last_date_epoch=strptime(last_date,"%d %b %Y"),verified_epoch=strptime(verified_date,"%d %b %Y")
| eval output = if( verified_epoch >=first_date_epoch AND verified_epoch<=last_date_epoch,"Phase1","Phase2")

[renuka]

Thank you so much ,I get the answers requried

[richgalloway]

Please explain the relationship among first date, last date, execution date, and verified. The text says verified of "Phase 1" is when execution date = 3 Feb 2020, but the sample output shows "Phase 1" for all execution dates.

[renuka]

we have implementation field where it contains values Phase 1 And Phase 2

First Date  And Last Date  will extract through lookup.
we have  separate mapping. csv  file where we have the phase 1 and phase 2  dates

we need to extract  verified column by comparing the first and last date with execution date
 if execution date is in between the First date and last date it should give me that phase

example: if my execution date is 03 Feb 2020

it is coming in the duration of Phase 1 i.e Phase1= 01 jan 2020-01mar 2020

then we get output verified=phase1

[richgalloway]

See if this helps.  To compare dates, they must first be converted into integers.

...
| eval eFirstDate = strptime(first_date, "%d %b %Y"), eLastDate = strptime(last_date, "%d %b %Y"), eExecDate = strptime(execution_date, "%d %b %Y")
| eval verified = case(eExecDate < eLastDate, "Phase 1", 1==1, "Phase 2")
...

[renuka]

Thank you ,but is not giving me the any output

[richgalloway]

My answer was a partial query. You must supply the beginning part to fetch data from the indexers.
Please share your full query.