I've set up a database lookup, but it's not returning any results; it should be returning 5 events. Here are the scenarios (and responses) I've tried (yes, some of them are pretty basic/dumb).
| lookup SMBkSalesCF
Error in 'lookup' command: Must specify one or more lookup fields.
| lookup SMBkSalesCF ValueCode
No results found.
| lookup SMBkSalesCF ValueCode ValueDesc
Error in 'lookup' command: All of the fields in the lookup table are specified as lookups, leaving no destination fields.
| lookup SMBkSalesCF ValueCode ValueDesc output MyFields
Error in 'lookup' command: Could not find all of the specified destination fields in the lookup table.
| lookup SMBkSalesCF ValueCode ValueDesc output ValueCode
No results found.
| lookup SMBkSalesCF ValueCode output ValueCode
No results found.
| lookup SMBkSalesCF ValueCode ValueDesc output ValueCode ValueDesc
No results found.
I've built this file statically as a CSV, and it looks like this:
ValueCode,ValueDesc
A,1-10
B,11-20
C,21-50
D,51-500
E,501+
Practical use-case scenario:
| dbquery MyDatabase "select MyField1, MyField2 from MyTable" | lookup SMBkSalesCF ValueCode as MyField2 OUTPUT ValueDesc
Sample data return expected:
MyField1 | MyField2 | ValueDesc
1 | A | 1-10
2 | C | 21-50
3 | C | 21-50
4 | B | 11-20
5 | E | 500+
Actual returned (okay, having problems getting the table for the actual results to display, but basically, MyField1 and MyField2 match the expected results, but ValueDesc is empty):
MyField1 | MyField2 | ValueDesc
1 | A |
2 | C |
3 | C |
4 | B |
5 | E |
Maybe I'm misunderstanding how database lookups work, but I'm expecting to get the textual value (ValueDesc) for the code value (ValueCode/MyField2) for each of those records. Instead, I get zilch.
What am I doing wrong?
D'oh! The problem was that MyField2 is actually storing the "ValueKey" table value, NOT the "ValueCode" table value. "ValueKey" is numeric (1, 2, 3, 4, 5) and "ValueCode" is alpha (A, B, C, D, E). So of course, there was no match!
I added "ValueKey" to the lookup and changed the command to:
| dbquery MyDatabase "select MyField1, MyField2 from MyTable" | lookup SMBkSalesCF ValueKey as MyField2 OUTPUT ValueDesc
And got the expected results set.
D'oh! The problem was that MyField2 is actually storing the "ValueKey" table value, NOT the "ValueCode" table value. "ValueKey" is numeric (1, 2, 3, 4, 5) and "ValueCode" is alpha (A, B, C, D, E). So of course, there was no match!
I added "ValueKey" to the lookup and changed the command to:
| dbquery MyDatabase "select MyField1, MyField2 from MyTable" | lookup SMBkSalesCF ValueKey as MyField2 OUTPUT ValueDesc
And got the expected results set.
This is what the "Actual results returned" is supposed to be. For some reason, when I edit the question, it has this in there, but when you actually view the question, it's doing something completely different...
MyField1 | MyField2 | ValueDesc
1 | A |
2 | C |
3 | C |
4 | B |
5 | E |