Select a column (named as days of the week) and copy over to a new column

[rahul0621]

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.

[bowesmana · Accepted solution]

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

[rahul0621]

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?

[bowesmana]

Sure, just add this line after the fields - wkday

| eval column=if(column="FILENAME",column,"Count")

[rahul0621]

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.

[bowesmana]

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!