How to map values in case statement?

[karthi2809]

Hi All,

I am using case statement to map values instead of other values. But i am not getting the values.I am getting UNknown values.
BucketFolder values is like:

inbound/concur

|rename  bucketFolder as BucketFolder| eval  InterfaceName=case(BucketFolder="%inbound%epm%","EPM", BucketFolder="%inbound%KPIs%","APEX_File_Upload", BucketFolder="%inbound%concur%","ConcurFile_Upload ",true(),"Unknown")| stats   values(InterfaceName) as InterfaceName   min(timestamp) as Timestamp values(BucketFolder) as BucketFolder   values(Status) as Status by correlationId  
| table Status  InterfaceName Timestamp FileName Bucket BucketFolder correlationId 

[yuanliu · Accepted solution]

There are multiple issues with this search.  But first, let me clarify your use case.  For events where bucketFolder has exemplified value of "inbound/concur", you want to assign "ConcurFile_Upload" as value of Interface; if bucketFolder is "<blah>inbound<bleh>epm<blih>", you want to assign "EPM" to Interface, and so on.  Is this correct?  In other words, given this dataset

bucketFolder	correlationId
inbound/concur	1
inbound/epm	1
inbound/KPIs	2
inbound/epm	2

you want the output to be

Status	InterfaceName	Timestamp	FileName	Bucket	BucketFolder	correlationId
	ConcurFile_Upload EPM				inbound/concur inbound/epm	1
	APEX_File_Upload EPM				inbound/KPIs inbound/epm	2

(Take care to clarify such carefully and in plain language and data illustration the next time you ask a question.  Using SPL to represent use case is often self defeating, even more so when you already know the SPL doesn't give you desired results.  You are basically inviting volunteers to read your mind, and mind-reading is not only painful for volunteers, but most often leads to wrong conclusions.)

Assuming that the above is a faithful representation of your requirement, we can discuss SPL problems.  But before that, I want to hammer on use case/requirement even further.  Do you really mean to map ""<blah>inbound<bleh>epm<blih>"" instead of simply "inbound/epm"? In other words, are wildcard uses like   "%inbound%epm%" truly essential to your dataset?  Decisions like this bears a lot of weight on optimal solution, sometimes can change suitable solution, too.  In the following, I will assume that you really, really want wildcards as your original SPL implied. (But I hope that's not the case.)

Now, to SPL diagnosis.  The first problem is, you cannot use wildcard in the righthand side of equal sign (=) outside of search context.  case function is not a search context.  Secondly, percent sign (%) is not a wildcard in the righthand side of equal sign.  As such, @tej57 's suggestion of using LIKE is correct.  You can also use searchmatch as @gcusello suggested, but use it directly as boolean AND use asterisk (*) instead of percent sign as wildcard.

| rename bucketFolder as BucketFolder 
| eval InterfaceName=case(searchmatch("BucketFolder = *inbound*epm*"),"EPM", searchmatch("BucketFolder = *inbound*KPIs*"), "APEX_File_Upload", searchmatch("BucketFolder = *inbound*concur*"), "ConcurFile_Upload", true(),"Unknown") 
| stats values(InterfaceName) as InterfaceName min(timestamp) as Timestamp values(BucketFolder) as BucketFolder values(Status) as Status by correlationId 
| table Status InterfaceName Timestamp FileName Bucket BucketFolder correlationId

You can also use regex match.

| rename bucketFolder as BucketFolder 
| eval InterfaceName=case(match(BucketFolder, "inbound.*epm"),"EPM", match(BucketFolder, "inbound.*KPIs"), "APEX_File_Upload", match(BucketFolder, "inbound.*concur"), "ConcurFile_Upload", true(),"Unknown") 
| stats values(InterfaceName) as InterfaceName min(timestamp) as Timestamp values(BucketFolder) as BucketFolder values(Status) as Status by correlationId 
| table Status InterfaceName Timestamp FileName Bucket BucketFolder correlationId

Each of these three can give the above illustrated results table using the following emulation that gives the mock data table illustrated above

| makeresults format=csv data="bucketFolder,correlationId
inbound/concur,1
inbound/epm,1
inbound/KPIs,2
inbound/epm,2"
``` data emulation above ```

Play with it and compare with real data.

---

[tej57]

Hello @karthi2809,

I'm not sure if searchmatch function can work with case condition or not. I've read that it must be used inside if condition. Can you try running the following search:

| rename bucketFolder as BucketFolder 
| eval InterfaceName=case(BucketFolder like "%inbound%epm%","EPM", BucketFolder like "%inbound%KPIs%","APEX_File_Upload", BucketFolder like "%inbound%concur%","ConcurFile_Upload",true(),"Unknown") 
| stats values(InterfaceName) as InterfaceName min(timestamp) as Timestamp values(BucketFolder) as BucketFolder values(Status) as Status by correlationId 
| table Status InterfaceName Timestamp FileName Bucket BucketFolder correlationId

Thanks,
Tejas.

---

If the above solution helps, an upvote is appreciated.

[gcusello]

Hi @karthi2809,

you can use % only using the like function otherwise you have to use *:

| rename  bucketFolder as BucketFolder
| eval  InterfaceName=case(
     BucketFolder=searchmatch("inbound") AND searchmatch("epm"), "EPM",
     BucketFolder=searchmatch("inbound") AND searchmatch("KPIs"), "APEX_File_Upload", 
     BucketFolder=searchmatch("inbound") AND searchmatch("concur"), "ConcurFile_Upload",
     true(), "Unknown")
| stats 
     values(InterfaceName) AS InterfaceName   
     min(timestamp) AS Timestamp 
     values(BucketFolder) AS BucketFolder   
     values(Status) AS Status 
     BY correlationId  
| table Status  InterfaceName Timestamp FileName Bucket BucketFolder correlationId 

Ciao.

Giuseppe

[karthi2809]

I am getting below error.

Error in 'EvalCommand': Type checking failed. The '==' operator received different types.