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.
Instead do this:
... | lookup TEST_MXTIMING_NICKNAME NICKNAME
| eval NICKNAME=coalesce(Human_Name_Nickname, NICKNAME)
| fields - Human_Name_Nickname
Instead do this:
... | lookup TEST_MXTIMING_NICKNAME NICKNAME
| eval NICKNAME=coalesce(Human_Name_Nickname, NICKNAME)
| fields - Human_Name_Nickname
Brill - Cheers 🙂
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
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.