Add Condition to Exclude 'searchTerm' in Property Statement

Tobias · 2024-10-30 14:52:31.263867+00

how do I add the condition that either property in the following statement must not contain the term 'searchTerm': if(defined(@"""{"joinPropertyType":"list","label":"addedFilterValues","propertyDefaultType":"string","resourceType":"event","type":"list","value":"addedFilterValues"}"""@), @"""{"joinPropertyType":"list","label":"addedFilterValues","propertyDefaultType":"string","resourceType":"event","type":"list","value":"addedFilterValues"}"""@,@"""{"label":"Filter Name","propertyDefaultType":"string","resourceType":"event","type":"string","value":"addedFilter"}"""@ )

Add Condition to Exclude 'searchTerm' in Property Statement | Mixpanel Community

8 comments

· Sorted by Oldest

kapa.ai
APP
·
·
Hi Tobias, Please stand by as I search the Mixpanel knowledge base for an AI-powered answer. I normally take about 15 seconds or so. In case AI cannot answer your question, feel free to reach out to Mixpanel support at mixpanel.com/get-support. If you have follow-up questions for this thread, please tag me again with your question: @kapa.ai <follow-up question> 👋
Feel free to ask a follow-up question once I've answered by mentioning me with @kapa.ai
kapa.ai
APP
·
·
To add the condition that either property in the given statement must not contain the term 'searchTerm', you can use the not function along with the in operator. Here's how you can modify the statement: LBKSif(defined(@"""{"joinPropertyType":"list","label":"addedFilterValues","propertyDefaultType":"string","resourceType":"event","type":"list","value":"addedFilterValues"}"""@), if(not('searchTerm' in @"""{"joinPropertyType":"list","label":"addedFilterValues","propertyDefaultType":"string","resourceType":"event","type":"list","value":"addedFilterValues"}"""@), @"""{"joinPropertyType":"list","label":"addedFilterValues","propertyDefaultType":"string","resourceType":"event","type":"list","value":"addedFilterValues"}"""@, if(not('searchTerm' in @"""{"label":"Filter Name","propertyDefaultType":"string","resourceType":"event","type":"string","value":"addedFilter"}"""@), @"""{"label":"Filter Name","propertyDefaultType":"string","resourceType":"event","type":"string","value":"addedFilter"}"""@, null ) ), if(not('searchTerm' in @"""{"label":"Filter Name","propertyDefaultType":"string","resourceType":"event","type":"string","value":"addedFilter"}"""@), @"""{"label":"Filter Name","propertyDefaultType":"string","resourceType":"event","type":"string","value":"addedFilter"}"""@, null ) )LBKSThis modification uses the not and in functions, which are available in Mixpanel's custom property formulas as documented here. The not function returns values that are not true, and the in operator checks if a value is contained within another value.The modified statement does the following: 1. It first checks if the addedFilterValues property is defined. 2. If it is defined, it checks if 'searchTerm' is not in addedFilterValues. If true, it returns addedFilterValues. 3. If 'searchTerm' is in addedFilterValues, it then checks if 'searchTerm' is not in the addedFilter property. If true, it returns addedFilter. 4. If 'searchTerm' is in both properties, it returns null. 5. If addedFilterValues is not defined, it checks if 'searchTerm' is not in the addedFilter property. If true, it returns addedFilter, otherwise it returns null.This ensures that the statement will only return a property value if it does not contain 'searchTerm'.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Tobias
·
·
kapa.ai please format this in a way that i can post it into the formula builder
kapa.ai
APP
·
·
Let me check the knowledge base for you again. One moment! 📚
kapa.ai
APP
·
·
Certainly! I'll format the formula in a way that you can directly paste into Mixpanel's formula builder. Here's the formatted version: LBKSif(defined(properties["addedFilterValues"]), if(not("searchTerm" in properties["addedFilterValues"]), properties["addedFilterValues"], if(not("searchTerm" in properties["Filter Name"]), properties["Filter Name"], null ) ), if(not("searchTerm" in properties["Filter Name"]), properties["Filter Name"], null ) )LBKSThis format is compatible with Mixpanel's custom property formula builder, as it uses the correct syntax for referencing properties (properties["property_name"]) and the appropriate functions (if, defined, not, in) as documented in Mixpanel's custom properties reference.You can now copy this formula and paste it directly into the formula builder in Mixpanel. This will create a custom property that checks if either "addedFilterValues" or "Filter Name" does not contain the term "searchTerm", returning the appropriate value or null based on the conditions we discussed earlier.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Tobias
·
·
kapa.ai the builder doesn't recognize 'null' as valid - what ist the right syntax ?
kapa.ai
APP
·
·
Let me check the knowledge base for you again. One moment! 📚
kapa.ai
APP
·
·
I apologize for the confusion in my previous response. You're right that null might not be recognized in Mixpanel's formula builder. Let's correct this using the proper Mixpanel syntax.In Mixpanel, instead of null, we should use an empty string "" or the undefined value. Based on the Mixpanel documentation on falsy values, both undefined and null are parsed as (not set) in the UI.Here's the corrected formula using an empty string: LBKSif(defined(properties["addedFilterValues"]), if(not("searchTerm" in properties["addedFilterValues"]), properties["addedFilterValues"], if(not("searchTerm" in properties["Filter Name"]), properties["Filter Name"], "" ) ), if(not("searchTerm" in properties["Filter Name"]), properties["Filter Name"], "" ) )LBKSThis formula uses an empty string "" instead of null. This should be recognized by the Mixpanel formula builder and will effectively represent a "not set" or null value in your reports.If you prefer to use undefined instead of an empty string, you can replace the "" with undefined in the formula. Both should work in Mixpanel's context.Remember, as per the Mixpanel documentation, it's generally recommended that for instances where a property value is essentially considered unavailable (like null or N/A), the property should not be sent with the event or user profile. However, in a formula like this, using an empty string or undefined is a suitable way to handle these cases.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

kapa.ai
APP
·
·
Hi Tobias, Please stand by as I search the Mixpanel knowledge base for an AI-powered answer. I normally take about 15 seconds or so. In case AI cannot answer your question, feel free to reach out to Mixpanel support at mixpanel.com/get-support. If you have follow-up questions for this thread, please tag me again with your question: @kapa.ai <follow-up question> 👋
Feel free to ask a follow-up question once I've answered by mentioning me with @kapa.ai
kapa.ai
APP
·
·
To add the condition that either property in the given statement must not contain the term 'searchTerm', you can use the not function along with the in operator. Here's how you can modify the statement: LBKSif(defined(@"""{"joinPropertyType":"list","label":"addedFilterValues","propertyDefaultType":"string","resourceType":"event","type":"list","value":"addedFilterValues"}"""@), if(not('searchTerm' in @"""{"joinPropertyType":"list","label":"addedFilterValues","propertyDefaultType":"string","resourceType":"event","type":"list","value":"addedFilterValues"}"""@), @"""{"joinPropertyType":"list","label":"addedFilterValues","propertyDefaultType":"string","resourceType":"event","type":"list","value":"addedFilterValues"}"""@, if(not('searchTerm' in @"""{"label":"Filter Name","propertyDefaultType":"string","resourceType":"event","type":"string","value":"addedFilter"}"""@), @"""{"label":"Filter Name","propertyDefaultType":"string","resourceType":"event","type":"string","value":"addedFilter"}"""@, null ) ), if(not('searchTerm' in @"""{"label":"Filter Name","propertyDefaultType":"string","resourceType":"event","type":"string","value":"addedFilter"}"""@), @"""{"label":"Filter Name","propertyDefaultType":"string","resourceType":"event","type":"string","value":"addedFilter"}"""@, null ) )LBKSThis modification uses the not and in functions, which are available in Mixpanel's custom property formulas as documented here. The not function returns values that are not true, and the in operator checks if a value is contained within another value.The modified statement does the following: 1. It first checks if the addedFilterValues property is defined. 2. If it is defined, it checks if 'searchTerm' is not in addedFilterValues. If true, it returns addedFilterValues. 3. If 'searchTerm' is in addedFilterValues, it then checks if 'searchTerm' is not in the addedFilter property. If true, it returns addedFilter. 4. If 'searchTerm' is in both properties, it returns null. 5. If addedFilterValues is not defined, it checks if 'searchTerm' is not in the addedFilter property. If true, it returns addedFilter, otherwise it returns null.This ensures that the statement will only return a property value if it does not contain 'searchTerm'.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Tobias
·
·
kapa.ai please format this in a way that i can post it into the formula builder
kapa.ai
APP
·
·
Let me check the knowledge base for you again. One moment! 📚
kapa.ai
APP
·
·
Certainly! I'll format the formula in a way that you can directly paste into Mixpanel's formula builder. Here's the formatted version: LBKSif(defined(properties["addedFilterValues"]), if(not("searchTerm" in properties["addedFilterValues"]), properties["addedFilterValues"], if(not("searchTerm" in properties["Filter Name"]), properties["Filter Name"], null ) ), if(not("searchTerm" in properties["Filter Name"]), properties["Filter Name"], null ) )LBKSThis format is compatible with Mixpanel's custom property formula builder, as it uses the correct syntax for referencing properties (properties["property_name"]) and the appropriate functions (if, defined, not, in) as documented in Mixpanel's custom properties reference.You can now copy this formula and paste it directly into the formula builder in Mixpanel. This will create a custom property that checks if either "addedFilterValues" or "Filter Name" does not contain the term "searchTerm", returning the appropriate value or null based on the conditions we discussed earlier.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Tobias
·
·
kapa.ai the builder doesn't recognize 'null' as valid - what ist the right syntax ?
kapa.ai
APP
·
·
Let me check the knowledge base for you again. One moment! 📚
kapa.ai
APP
·
·
I apologize for the confusion in my previous response. You're right that null might not be recognized in Mixpanel's formula builder. Let's correct this using the proper Mixpanel syntax.In Mixpanel, instead of null, we should use an empty string "" or the undefined value. Based on the Mixpanel documentation on falsy values, both undefined and null are parsed as (not set) in the UI.Here's the corrected formula using an empty string: LBKSif(defined(properties["addedFilterValues"]), if(not("searchTerm" in properties["addedFilterValues"]), properties["addedFilterValues"], if(not("searchTerm" in properties["Filter Name"]), properties["Filter Name"], "" ) ), if(not("searchTerm" in properties["Filter Name"]), properties["Filter Name"], "" ) )LBKSThis formula uses an empty string "" instead of null. This should be recognized by the Mixpanel formula builder and will effectively represent a "not set" or null value in your reports.If you prefer to use undefined instead of an empty string, you can replace the "" with undefined in the formula. Both should work in Mixpanel's context.Remember, as per the Mixpanel documentation, it's generally recommended that for instances where a property value is essentially considered unavailable (like null or N/A), the property should not be sent with the event or user profile. However, in a formula like this, using an empty string or undefined is a suitable way to handle these cases.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.