How do you lookup matching numbers by values regardless of formats?

[xshen_anji]

I have a lookup table, mylookup.csv, such as:

Key, Value
3, 30
4, 45
5, 52

I have a CSV source mysource.csv, as:

sourceKey, Otherthings
3.0, 300
4.0, 400
5.0, 500

I do a search like: source=mysource.csv | lookup mylookup.csv Key as sourceKey OUPUT Value
This gives nothing. But if I change my lookup table's Key's format as 3.0, 4.0, etc, it could give results.

My question is if there is a way I can lookup and match number fields by their values, regardless of the the formats, whether there are trailing .0s. My situation is my customer would provide these lookup files with different formats(with or without .0s), I would accommodate all these situations.

[harsmarvania57]

Hi,

If you have only one type of format like with or without . (dot) then you can try below query. In below query rex will remove dot and any digit after dot from sourceKey field so it will match with Key field from mylookup.csv file.

| inputlookup mysource.csv
| rex field=sourceKey mode=sed "s/\.\d+//g"
| lookup mylookup.csv Key as sourceKey OUTPUT Value as Value

[xshen_anji]

Thank you for answer. But my problem is these lookup files's key formats are various, so even I change the source file's format to uniform, I still can't match successfully with different formatted lookup files.

[harsmarvania57]

Can you please provide all possible format type from mysource.csv if possible ?

[xshen_anji]

mylookup.csv's key field can be "3", "3.0", "3.00" etc. the mySource.csv's sourceKey also can be these different format. So even I uniform the source file, still can't match.

[harsmarvania57]

Ah so you don't have unique format in any of the lookup file ?

[harsmarvania57]

You can try below query

| inputlookup mysource.csv
| rex field=sourceKey mode=sed "s/\.\d+//g"
| join type=left sourceKey [ inputlookup append=t mylookup.csv | rex field=Key mode=sed "s/\.\d+//g" | rename Key as sourceKey]

[xshen_anji]

Thank you for this. I would try out later. I think it would work. But this seems complicated. I just wonder if there is no lookup or join by values for number fields. Just curious!