Hi I have a table similar to this:
Brand ID_EMP
Nike 123
Adidas 456
Lotto 123
other table like this:
code name
123 Smith
456 Myers
The result should be
Nike 123 Smith
Adidas 456 Myers
Lotto 123 Smith
but insted of this I'm getting:
Nike 123 Smith john
adidas 456 Myers. Mike
This is the query that I'm using.
| dbxquery query="SELECT * FROM Clients ;" connection="Clients"
| where NOT like(SAFE_NAME,"S0%")
|rename ID_EMP as code
| join type=left [| inputlookup append=t Emp.csv
| table code, name, STATUS
]| fillnull value="Blank" STATUS
| dedup code
| where STATUS!="Blank"
Please find the sample query with the data points, hope it helps you to change your query accordingly -
Also, in most of the cases you can skip using join command if one of your datasources is a lookupfile, you can use |lookup filename.csv command in those cases.
Considering the below is in lookupfile emp.,csv
code name
123 Smith
456 Myers
Final Query -
| makeresults
| eval BRAND_EMPID="Nike,123;Adidas,456;Lotto,123"
| makemv delim=";" BRAND_EMPID
| mvexpand BRAND_EMPID
| eval Brand=mvindex(split(BRAND_EMPID,","),0)
| eval ID_EMP=mvindex(split(BRAND_EMPID,","),1)
| table Brand ID_EMP
| lookup emp.csv Code AS ID_EMP
| table Brand ID_EMP NAME
Let me know if the above query helped or if you feel anything needs to be changed in the above example.