How to Join Alert lookup file with a mapping file depending on pattern search?

[jinishshah]

Hello,

I have a alert dump data Horizon.csv having important columns like below:

Alert   GRN   Type ....

PNC/hz-hfp-l-abc[MAXRUN]      PNC/hz-hfp-l-abc   Autosys

Filesystem[ivp1234.xy.com] [91>90]   ivp1234.xy.com   Application

Filesystem[ivp1244.xy.com] [91>90]   ivp1244.xy.com   Application

p.start.script.pl is down     Process down   API

which I need to merge with Mapping.csv but on a condition that if Type=Autosys then merge on GRN else merge on Type

details of Mapping.csv

Type   Name   Module    Header

Autosys   hz-hfp-l-abc   HF   EOD Job

Application   <blank>   Eng   Server alerts

API   <blank>   LF   Service alerts   

I need output as 

Alert   GRN   Type   Module   Header

PNC/hz-hfp-l-abc[MAXRUN]      PNC/hz-hfp-l-abc   Autosys   HF   EOD Job

Filesystem[ivp1234.xy.com] [91>90]   ivp1234.xy.com   Application   Eng   Server alerts

Filesystem[ivp1244.xy.com] [91>90]   ivp1244.xy.com   Application   Eng   Server alerts

p.start.script.pl is down     Process down   API   LF Service alerts

[jinishshah]

Thanks for your quick reply @ITWhisperer . Merging for Type=Autosys is working fine but not for others. I am getting blank output in others. What if I change <blank> in Mapping.csv to "all". What will be the query then ?

[ITWhisperer · Accepted solution]

| inputlookup Horizon.csv
| eval Name=if(Type="Autosys",GRN,"all")
| lookup Mapping.csv Type Name

[jinishshah]

Hey @ITWhisperer , it worked for Module column but not working as expected for Header column. Picking up wrong value

[ITWhisperer]

In what way is it wrong? What are you getting? What are you expecting?

[jinishshah]

Sorry My Bad.....Its works Superbly !!! Thanks for your quick answer !!! Really appreciated !!

[ITWhisperer]

Assuming <blank> fields are blank string and not null, try something like this

| inputlookup Horizon.csv
| eval Name=if(Type="Autosys",GRN,"")
| lookup Mapping.csv Type Name