Can you help me update some field values from a lookup table?

[pench2k19]

Hi Team,

I have the following field values in a look up file

BUS_DT+1,11:00
BUS_DT+0,12:00

i want to update the field values into my query result. When there is BUS_DT+1 value, it should replace today's date in yyyy-mm-dd format, and when there is BUS_DT+0 value, it should print yesterday's date.

The expected result respectively is

2019-02-04, 11:00
2019-02-03,12:00

can anyone help on this use case. Thank you in advance

@vnravikumar @jkat54 @woodcock

[jkat54]

BUS_DT+1,11:00
BUS_DT+0,12:00

| rex “\+(?<dayCount>\d+)\,(?<hourCount>\d+):(?<minuteCount>\d+)”
| eval _time=case(dayCount==1,now()+86400, dayCount==0,now(),1==1,0)
| eval newTimeStamp=strftime(strptime(_time, “%s),”%y-%m-%d, %H:%M:%S)

[pench2k19]

the folloiwng giving me error @jkat54

| inputlookup SLA.csv | rex field=SOR_TDQ_FAIL_SLA_THRESHOLD “+(?\d+)\,(?\d+):(?\d+)”
| eval _time=case(dayCount==1,now()+86400, dayCount==0,now(),1==1,0)
| eval newTimeStamp=strftime(strptime(_time, “%s),”%y-%m-%d, %H:%M:%S) |table newTimeStamp

[vnravikumar · Accepted solution]

Hi @pench2k19

Try this and let me know. I considered two fields in lookup with date and time.

date,time
BUS_DT+1,11:00
BUS_DT+0,12:00


| inputlookup panel.csv 
| eval date1 = case(date="BUS_DT+1",strftime(now(),"%Y-%m-%d").",".time,date="BUS_DT+0",strftime(relative_time(time(), "-d"),"%Y-%m-%d".",".time))

[pench2k19]

@vnravikumar sorry for making you confused..its not two fields , the values belongs to single field

sl_time
BUS_DT+1,11:00
BUS_DT+0,12:00

[vnravikumar]

Hi @pench2k19

try this

Updated

| inputlookup panel.csv | rex field="sl_time" "(?P<date>.*)\,(?<time>.*)" | eval sl_time = case(date="BUS_DT+1",strftime(now(),"%Y-%m-%d").",".time,date="BUS_DT+0",strftime(relative_time(time(), "-d"),"%Y-%m-%d".",".time))

[vnravikumar]

try above query and let me know

[pench2k19]

this works..thank you

[vnravikumar]

welcome 🙂