I have a lookup CSV table that lists dates of holidays in a single column.
HolidayTable.csv:
HDate |
1/1/2020 |
................... |
I tried to use the following:
search ...
| eval currentdate=strftime(now(),"%-m/%-d/%Y")
| lookup HolidayTable.csv HDate as currentdate OUTPUT HDate as Holiday
| eval Holiday=if(isnull(Holiday), "N", "Y")
My expectation was that when the current date matches a row in the table I will get the date string and otherwise - null.
It does not seem to work this way.
What am I missing here?
[Following up with my own solution]
I ended up with creating a dummy 2nd column.
HDate | Off |
1/1/2020 | Y |
................... | Y |
And with a slight change (used new column as returned value) my code started working:
search ...
| eval currentdate=strftime(now(),"%-m/%-d/%Y")
| lookup HolidayTable.csv HDate as currentdate OUTPUT Off as Holiday
| eval Holiday=if(isnull(Holiday), "N", "Y")
[Following up with my own solution]
I ended up with creating a dummy 2nd column.
HDate | Off |
1/1/2020 | Y |
................... | Y |
And with a slight change (used new column as returned value) my code started working:
search ...
| eval currentdate=strftime(now(),"%-m/%-d/%Y")
| lookup HolidayTable.csv HDate as currentdate OUTPUT Off as Holiday
| eval Holiday=if(isnull(Holiday), "N", "Y")
Your currentdate date format is wrong based on your example of 01/01/2020 in the CSV. Your format is
%-m/%-d
but it should be %m/%s, so it has leading zeros
It was a typo in my question. Month and day in my table are without leading zeroes.
Since the table is going to be maintained via Excel, I had to use the default date format.
I will update the question.