Hi,
I am trying to enter an expression to compare two strings, greater than or less than, but it sends me the following error:
Arithmetic expression ‘([Location] > “A”)’ does not have valid input types
I think this comparison only accepts numbers or dates.
Is there any way to do it with strings?
Yo uare not telling enough information of what you want.
Assuming you want to compare how long are the strings (which are numbers) you can use <, > or =
# If [StringA] is "Hello" and [StringB] is "Hola"
LEN([StringA])>LEN([StringB]) # Would return true
LEN([StringA])<LEN([StringB]) # Would return false
LEN([StringA])=LEN([StringB]) # Would return false
I don’t know the answer, and will be interested to hear others’ advice and maybe try some techniques myself later.
Meanwhile, an alternative idea would be to compare the sequence of your sorted and unsorted list of 2 items. Maybe something like:
CONCATENATE(LIST(“A”, [Location])) = CONCATENATE(SORT(LIST(“A”, [Location])))
I want to compare if “Gaby Gcia” > “Pedro”, like a sort
Another solution here is to create a separate table for these options…
Then you can OrderBy(), Sort(), Count(), Max() or whatever based on the actual numbers for the corresponding selection.
Stealing @Steve 's idea and applying it here:
Let’s assume that the first name is on column [One] and the second… on [Two]:
Is [One] alphabetically first than [Two]?, or
Is [One] before on the list compared to [Two]?:
COUNT(
SPLIT(
INDEX(
SPLIT(
CONCATENATE(
" , ",
SORT(LIST([One], [Two])),
" , "
),
CONCATENATE(
" , ",
[One],
" , "
)
),
1
),
" , "
)
)<
COUNT(
SPLIT(
INDEX(
SPLIT(
CONCATENATE(
" , ",
SORT(LIST([One], [Two])),
" , "
),
CONCATENATE(
" , ",
[Two],
" , "
)
),
1
),
" , "
)
)
Why?
ty but it is too complex and too many functions for each row,
But I’m worried about the performance
ty but I need to compare each record
ty, I like this solution, it does not involve so many functions
But I’m worried about the performance
actually I need to check that a text is in a range of two texts, like text1 between text 2 and text3
or text2 <= text1 and text1 <= text3
where
text1 is a field of a table
text2 and text3 are user variables
I will put this condition in the filter of a slice
FWIW, my understanding from various other discussions in the community is that the text expressions used in my idea as well as other replies in this conversation generally do not present performance concerns.
In case it seems preferable for any reason, here’s an alternative version of the idea I suggested trying:
INDEX(LIST([text1], [text2]), 1) = INDEX(SORT(LIST([text1], [text2])), 1)
Another variation to try given your specific context of needing to evaluate the alphabetical sequence of 1 text value vis à vis 2 other text values:
INDEX(SORT(LIST([text1], [text2], [text3])), 2) = [text 1]
Again: why? Why do you need to do this comparison? What are you trying to accomplish? We might be able to offer an alternative if we had an idea what you are trying to do.
I have a warehouse where each position has a name, and the users of my app are assigned a range of positions, and I have a spreadsheet with tasks to collect products in my warehouse, but the users can only collect products from the assigned range.
example:
my positions are called A1, A2, A3, B1, B2, B3, C1, C2, C3,…Z1,Z2,Z3
USER1 only collects products from positions between A1 and B3
USER2 only collects products from the positions that are between C1 and D3
position names are variable and can grow as users can change range
Got it. Unfortunately, I think you’ll have to approach this in a different way. Instead of:
AND(([Location] >= "A1"), ([Location] <= "B3"))
Consider:
IN([Location], LIST("A1", "A2", "A3", "B1", "B2", "B3"))
Or:
IN(LEFT([Location], 1), LIST("A", "B"))
Or:
CONTAINS("AB", LEFT([Location], 1))
ty but to do this I have to have the list of locations that are in a range, I thing it is no posible