Hello,
I have an inputlookup table (test.csv) with a few columns including 7 columns (for 7 days of the week) as shown below.
FILENAME | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
abc | 1 | 2 | 3 | 4 | 5 | X | X |
xyz | 11 | 2 | 30 | 4 | 5 | X | X |
123 | 111 | 2 | 300 | 40 | 5 | X | X |
I need to pull the column corresponding to the execution day. For example, if i execute it on 6/23/2020 (date being Wednesday), I should get something like this.
FILENAME | Count |
abc | 3 |
xyz | 30 |
123 | 300 |
If I run this search on 6/27/2020, being a Saturday, I should get something like this -
FILENAME | Count |
abc | X |
xyz | X |
123 | X |
I tried something like this but it isn't working -
| inputlookup test.csv | eval wkday = strftime(now(),"%A") | eval Count = {wkday}
Any help would be greatly appreciated.
One way
| inputlookup test.csv
| transpose
| eval wkday = strftime(now(),"%A")
| where column="FILENAME" OR column=wkday
| fields - wkday
| transpose header_field=column
| fields - column
One way
| inputlookup test.csv
| transpose
| eval wkday = strftime(now(),"%A")
| where column="FILENAME" OR column=wkday
| fields - wkday
| transpose header_field=column
| fields - column
Thanks for your response @bowesmana
Here's one problem with this solution - the column name will keep on changing (with wkday) but I want the output column name always to be "Count".
Could this be rectified?
Sure, just add this line after the fields - wkday
| eval column=if(column="FILENAME",column,"Count")
I used this instead after fields - wkday:
replace "*day" with Count in column
But your solution worked perfectly for me. Thanks a lot for the help.
Haha, yes, you will find that for ever task, there are often several solutions. You could also use the eval replace() function.
Have fun exploring!