I have an interesting lookup table problem. I essentially want to unpivot a lookup table (in other words I have multiple fields in a lookup table, but only 1 field in the table that will be created. Conditions will determine which lookup field populates that table field).
In my data I have two fields of the following form:
Table.....Status
A............delete
A............insert
B............delete
B............insert
The look up table is of the following form:
Table.....Insert_Status.....Delete_Status
A............yes....................no
B............yes....................yes
If I run this normally as I do a lookup table:
lookup $LookupName$ Table as Table OUTPUTNEW Insert_Status as Insert_Status
I will get 4 columns that look like the following
Table.....Status...........Insert_Status.....Delete_Status
A............delete...........yes......................no
A............insert............yes......................no
B............delete...........yes......................yes
B............insert............yes......................yes
I want instead to have the output be the following:
Table.....Status.....3rd_Column
A............delete......no
A............insert.......yes
B............delete......yes
B............insert.......yes
There are 2 unattractive ways that I can get to this 3 column end result:
1) Have multiple A and B values for the Table field in the lookup table.
2) Use the lookup table to get the 4 column example and then add a transformation line of code that turns creates a 5th column that is in the proper format and then get rid of the other two columns.
I don't like 1, because it will leave a very messy lookup table and I want to keep that as clean as possible as other people will be filling it in. I feel like 2 is a very sloppy way of doing it. I have more than 2 Statuses and this could get really big really quick.
I'm wondering if there is some way that I could populate the third column based on the Status column. e.g. if Status is 'delete' then I use the respective 'Delete Status' lookup field and if the Status is 'insert' then I use the respective 'Insert Status' lookup field.
I know this is a long post, but hopefully it was clear. Thanks for taking the time to look at this.
Splunkster45
I would use a macro like this:
[InsertOrDeleteStatus(3)]
args = LookupName, Table, InsertOrDelete
definition = lookup $LookupName$ $Table$ AS Table OUTPUTNEW $InsertOrDelete$_Status AS Status
Then call it like this (you have to uppercase the first letter of Table; e.g. "delete"->"Delete"):
... | eval myTable= upper(substr(Table,1,1)).substr(Table,2) | `InsertOrDeleteStatus($LookupName$,Table,Status)` | field - myTable