I'm facing the following use case.
I've a search that return fields like:
- date (month/year)
- Availability Cluster
In a lookup table, I've the connection b/w date (month/Year) and Availability Clusters, like
Date Cluster 1 Cluster 2 Cluster 3
2016-01 100 200 300
2016-02 110 210 310
2016-03 120 220 320
Availability Clusters in the search and in the lookup have the same domain.
Now, i need the lookup in the table the value matching both date and Availability Cluster fields related to the events (so the column to be lookup is variable, depending on "Availability Cluster" field content).
How can i do?
Currently the lookup looks something like, don't know how to complete the statement
lookup lookupfile.csv Date as Date OUTPUTNEW ???
Ideas/ suggestions are really appreciated,
Do you control the format/generation of the lookup table? If yes, I would suggest to make the lookup table more linear with just 3 columns, like this
Date,Cluster,Value 2016-01,Cluster 1,100 2016-01,Cluster 2,200 2016-01,Cluster 3,300 ....
You'd also need to ensure that timestamp format in Date column matches your data exactly.
That way it'll be easy to lookup your data (
...| lookup lookupfile.csv Date as Date Cluster as "Availability Cluster" OUTPUT Value).
If you can't try this workaround (less efficient)
your current search giving fields date, AppID, "Availability Cluster"... | join type=left Date "Availability Cluster" [| inputlookup lookupfile.csv| untable Date Cluster Value | rename Cluster as "Availability Cluster"]
May i code a lookup with 2 parameters as input?
I imagine something like:
| lookup lookupfile.csv Date as Date, Cluster as Cluster OUTPUTNEW <<FIELD>>as <<FIELD>>
yes, so you can write as
your current search giving fields date, AppID, "Availability Cluster"...| lookup lookupfile.csv Date as Date, Cluster as "Availability Cluster" OUTPUTNEW Value