LOOKUP(LEFT([_THISROW].[Model Number],3), “Data Catagories”, “Model [String]”, “Category”)
having difficulty matching the (Model [String]) that contained 2 or 4 characters, this expression above works perfectly when only matching 3 characters. How can I make it work for the values that have 2 or 4 characters to match to. If I put 4 within the left expression above, it does not work at all)
Column [Model [String]] has values that have anywhere between 2 and 4 characters. So in my expression, if I put two in the left() I get all those with 2 characters that match. My problem is, how can I make it match the 2, 3, and 4 characters in the same expression
Hi @Rurrea
What about that?
ANY(
SELECT(Data Catagories[Category],
OR(
CONTAINS([Model [String]],LEFT([_THISROW].[Model Number],2)),
CONTAINS([Model [String]],LEFT([_THISROW].[Model Number],3)),
CONTAINS([Model [String]],LEFT([_THISROW].[Model Number],4))
)
)
For reference:
appreciate the help, but this expression does not work either.
OK, you may want to provide more information then? “does not work either” doesn’t help me into helping you.
Let’s see. With the contain function, it seems to mix and match the wrong models.
The lookup function works great, with the exception that I can only do 2 characters, 3 characters, or 4 characters at once. Looking for a way to have it read all three situations. The majority are three characters in the Model [String] column but I don’t thing that makes a difference.
Can you provide examples? Screenshot are best.
so with contains function, it seems to match the 2 to 4 character values more than once.
For example, a value in [Model [String]] that has only two characters seems to match with models in left([_thisrow].[Model Number]2 , left([_thisrow].[Model Number]3 , and left([_thisrow].[Model Number]4. Creating an uncontrollable result.
For example: the two values EAT and EATD in [Model [String]]. EAT ends up falling into multiple categories because it will match in the left()4 and left()3 expressions above.
Thank you for the patience, I hope this helps explain my scenario a bit better.
I’m unsure how to write it, but it seems like I need the values with two characters to only search the contains([Model [String]], left([_thisrow].[Model Number],2)), the values with three characters to only search the contains([Model [String]], left([_thisrow].[Model Number],3)), … and so on
OK, thanks for the screenshots!
What you are willling to achieve requires a pretty-consuming expression.
Here is my suggestion:
IFS(
IN(LEFT([Model Number],4), Data Catagories[Model [String]],
LOOKUP(LEFT([Model Number],4), "Data Catagories", "Model [String]", "Category"),
IN(LEFT([Model Number],3), Data Catagories[Model [String]],
LOOKUP(LEFT([Model Number],3), "Data Catagories", "Model [String]", "Category"),
IN(LEFT([Model Number],2), Data Catagories[Model [String]],
LOOKUP(LEFT([Model Number],2), "Data Catagories", "Model [String]", "Category")
)
or:
IFS(
IN(LEFT([Model Number],4), Data Catagories[Model [String]],
LOOKUP(LEFT([Model Number],4), "Data Catagories", "Model [String]", "Category"),
IN(LEFT([Model Number],3), Data Catagories[Model [String]],
LOOKUP(LEFT([Model Number],3), "Data Catagories", "Model [String]", "Category"),
TRUE,
LOOKUP(LEFT([Model Number],2), "Data Catagories", "Model [String]", "Category")
)
For reference:
Tried it. But error says that the in() function is used incorrectly
I forgot a closing bracket. Can you try with this?
IFS(
IN(LEFT([Model Number],4), Data Catagories[Model [String]]),
LOOKUP(LEFT([Model Number],4), "Data Catagories", "Model [String]", "Category"),
IN(LEFT([Model Number],3), Data Catagories[Model [String]]),
LOOKUP(LEFT([Model Number],3), "Data Catagories", "Model [String]", "Category"),
TRUE,
LOOKUP(LEFT([Model Number],2), "Data Catagories", "Model [String]", "Category")
)
For reference: IN() - AppSheet Help
Error code: parameter 2 of function IN is of the wrong type
IN(item-to-search-for, list-to-search)
Can you confirm this part is a list?
Data Catagories[Model [String]]
Generally, you would want to avoid any blank space or square brackets in column and table names.
Unsure how to answer that.
I can rename the table and column name. What would be my next step?
Assuming you rename:
table “Data Catagories” ==> Data_Catagories
column “Model [String]” ==> Model_String
column “Model Number” ==> Model_Number
You would have an expression like this:
IFS(
IN(LEFT([Model_Number],4), Data_Catagories[Model_string]),
LOOKUP(LEFT([Model_Number],4), "Data_Catagories", "Model_string", "Category"),
IN(LEFT([Model Number],3), Data_Catagories[Model_string]),
LOOKUP(LEFT([Model_Number],3), "Data_Catagories", "Model_string", "Category"),
TRUE,
LOOKUP(LEFT([Model_Number],2), "Data_Catagories", "Model_string", "Category")
)
Where:
LEFT([Model_Number],4)
returns a text
Data_Catagories[Model_string]
returns a list of text
IN(LEFT([Model_Number],4), Data_Catagories[Model_string])
returns true or false
LOOKUP(LEFT([Model_Number],4), "Data_Catagories", "Model_string", "Category")
returns a text
I would suggest using virtual columns to test each part individually:
For reference:
