How to replace values without using a join

[robertlynch2020]

I am using a join, but is there a better way to replace values?

I have the following table. (NICKNAME + Human_Name_Nickname are the headers)

NICKNAME    Human_Name_Nickname
mx  MX_BASIC
smcrisk_engine  RISK_ENGINE
mxtraderepository_engine    MX_TRADE_REPO_ENGINE
smcobjectrepository_engine  SM_ENGINE
mxmlexchange_mxtaskxa   MXMLEXCHANGE
mxdealscanner_engine    DEAL_SCANNER
mx_cesar    CESAR
mx_marketdata_repository_engine MARKET_DATA
mxprocessingscript  PROCESSING_SCRIPT

I am retriving back thousands of lines of data with NICKNAME, i want to replace values from the lookup table.
E.G find "mx" and replace it with "MX_BASIC" etc.. so lots of entries. Then find "smcrisk_engine" and replace it with "RISK_ENGINE" if no match use the original value.

This is what i have- But i have been told not to use joins...can i do this better?

| join  NICKNAME type=left [inputlookup TEST_MXTIMING_NICKNAME.csv  ] | fillnull Human_Name_Nickname | eval Human_Name_Nickname=if(Human_Name_Nickname=0,$$NICKNAME$$,Human_Name_Nickname) | rename Human_Name_Nickname AS NICKNAME.

This works, but i am concerned of performance.

[woodcock · Accepted solution]

Instead do this:

... | lookup TEST_MXTIMING_NICKNAME NICKNAME
| eval NICKNAME=coalesce(Human_Name_Nickname, NICKNAME)
| fields - Human_Name_Nickname

[robertlynch2020]

Brill - Cheers 🙂

[gcusello]

Hi robertlynch2020,
lookup command is a left join so you can write something like this

your_search
| lookup TEST_MXTIMING_NICKNAME.csv NICKNAME OUTPUT other_lookup_fields
| ...

if the NICKNAME field is different between search and lookup, you can write

your_search
| lookup TEST_MXTIMING_NICKNAME.csv NICKNAME AS different_NICK_NAME OUTPUT other_lookup_fields
| ...

Bye.

Giuseppe

[richgalloway]

Joins do not perform well so it's a good idea to avoid them. What you are trying to do seem pretty straightforward and can easily be done without a join.

<your search that returns events with NICKNAME field> | lookup TEST_MXTIMING_NICKNAME.csv NICKNAME OUTPUT Human_Name_Nickname | eval NICKNAME=coalesce(Human_Name_Nickname,NICKNAME) | ...

This query searches the lookup file for the given NICKNAME and returns the associates Human_Name_Nickname. The coalesce statement sets NICKNAME to the value if Human_Name_Nickname if it is not null, otherwise it's set to itself.