In my lookup table, I have the days of the week as columns with "Y" or "N" in the field (not able to change this as this is how the data is provided). I would like to return only the results from the lookup table that matches the day of the week and the day of the week columns with "Y".
I have been looking at using IF statements and Where clauses, but not really getting it. The data columns look like this:
Mon | Tue | Wed | Thu | Fri | Sat | Sun |
Y | N | Y | N | N | N | N |
N | Y | N | Y | N | Y | N |
I have been extracting the day by using dayOfWeek=strftime(_time, "%a") from the imbeded search query:
| inputlookup somecsv.csv
| join email [search index=someindex | eval dayOfWeek=strftime(_time, "%a")]
| table or stats (data from inputlookup that matches the days of the week from the search)
This is where I get stuck. Any help will be greatly appreciated.
Hi @CSULeigh , not sure if I understand the ask completely, but have you tried
| inputlookup somecsv.csv
| join type=outer dayOfWeek [search index=someindex | eval dayOfWeek=strftime(_time, "%a")|eval found=1]
|where isnotnull(found)
Yes the table is an example. There is more data to the tables. Basically anything with a "Y" marked on the day column is when the class will be available. This lookup table has the class data and need to return the class data based on a search of survey data from an index that will have the day that needs to match the lookup data based on the day. I hope this helps.
Hi @CSULeigh , not sure if I understand the ask completely, but have you tried
| inputlookup somecsv.csv
| join type=outer dayOfWeek [search index=someindex | eval dayOfWeek=strftime(_time, "%a")|eval found=1]
|where isnotnull(found)