How to use a csv as a mapping for a table without the use of join

[phamxuantung]

Hi, I have a search that produce the following table

Organization|Amount|AcquirerBank
Or_A        |2000  |1234
Or_A        |4000  |2345
Or_B        |1200  |3456
            |4020  |4567
Or_C        |1456  |5678     

And then I have a csv file that provide the bank code with the bank name as a mapping csv as

AcquirerBank|BankName
1234        |BankA
2345        |BankB
4567        |BankC
5678        |BankD

The target table should look something like this

Organization|Amount|AcquirerBank|BankName
Or_A        |2000  |1234        |BankA
Or_A        |4000  |2345        |BankB
Or_B        |1200  |3456        |
            |4020  |4567        |BankC
Or_C        |1456  |5678        |BankD

 I try to use join like this

index=index
|table Organization, Amount, AcquirerBank
|join AcquirerBank
[inputlookup bank_mapping.csv
|table AcquirerBank, BankName]
|table Organization, Amount, AcquirerBank, BankName

But I encounter 2 problems:

1. My index have around a million events, and [join] have a limited number of events it can join, so my result table was lack in result.

2. Also [join] don't show enough results if the mapping csv don't have the data, as the example above, if I use [join], OrB with the field Acquirer that don't exist in mapping csv will not show up.

Anyone have a alternative to [join] that can resolve above problems?

Thank you in advance.

[ITWhisperer]

The lookup command does what you want

index=index
|table Organization, Amount, AcquirerBank
|lookup bank_mapping.csv AcquirerBank
|table Organization, Amount, AcquirerBank, BankName

https://docs.splunk.com/Documentation/Splunk/8.2.4/SearchReference/Lookup

[phamxuantung]

Sorry for the late reply, but the lookup command don't give me the desired results, I want the result divided to neat 1 line, instead it show like this

it should be in one line for each events.

ALTERNATEACQUIRER is the "Acquirerbank" in my original question.

[ITWhisperer]

There is obviously more to this search than you are letting on, which is fair enough. Having said that, you can collapse the multi-value fields into unique values using the values aggregator

| stats values(BANK_CODE) as BANK_CODE values(TCTQT) as TCTQT ... by ...

[phamxuantung]

I think the natural of my search is fairly simple.

I have an index that have around a million events, each event is a transaction information with needed information which is (_time, ALTERNATEACQUIRER, AMOUNT).

The final result need to be a table with each transaction separately on each row (for search and analyze purpose later), with the column: _time, ALTERNATEACQUIRER, AMOUNT, BANK_CODE, TCTQT.

I have a csv file name BANK_ACQ_BIN.csv that have ALTERNATEACQUIRER with corresponding BANK_CODE and TCTQT.

I try to use join before, but as aforementioned in my original post, [join] function, while it can produce a table satisfy my requirment, can only produce a limited number of events, and don't show the event that have ALTERNATEACQUIRER=null or don't have a matching ALTERNATEACQUIRER in the csv file.

I try to use Automate Lookup but it don't work either.

Sorry if I make you confuse reading this.