Hello,
I have a table named "Data Table", and a column called [Item Number] as below.
10001 |
10002 |
K0001 |
K0002 |
20001 |
20002 |
30001 |
I am trying to find out the max value which is starting with "2". The answer should be 20002 here.
However my formula as below result in 0:
MAX(LIST(0) + SELECT(Data Table[Item Number], left(Data Table[Item Number],1)="2"))
May I know how can I amend my formula to find out the max value which is starting with "2" in a text column. Thank you.
Solved! Go to Solution.
Hi @LawrenceMa
There are two steps, in two different tables.
1) In your table Data Table, you should have a virtual column "digitNumber" with this expression:
IF(STARTSWITH([5-digit Item Number], "2"),
ANY(EXTRACTNUMBERS([5-digit Item Number])),
0
)
2) in your other table, where you wish to get the max value over all the data set of Data Table, you can use this expression:
MAX(Data Table[digitNumber])
The syntax of your expression is wrong. It needs to be like this:
MAX(SELECT(Data Table[Item Number], left([Item Number],1)="2"))
There is also a STARTSWITH() function so you could do this:
MAX(SELECT(Data Table[Item Number], StartsWith([Item Number], "2")))
Hello WillowMobileSys,
But I got error message as below:
The inputs for function 'MAX' should be a list of numeric values
Just becasue the type of Data Table[Item Number] is Text. Any thoughts please?
Hi @LawrenceMa
I would add a virtual column for getting a numeric value first.
For example, let's name this virtual column "itemValue". Based in @WillowMobileSys 's suggestion, it would be:
IF(STARTSWITH([Item Number], "2"),
EXTRACTNUMBERS([Item Number]),
0
)
Then, your expression would be:
MAX(table[itemValue])
For reference:
EXTRACTNUMBERS() - AppSheet Help
Hello Aurelien,
I got error message when creating first given formula:
EXTRACT has invalid inputs
Any thoughts please?
My bad.
Can you try instead:
IF(STARTSWITH([Item Number], "2"),
ANY(EXTRACTNUMBERS([Item Number])),
0
)
EDIT:
@LawrenceMa wrote:
I got error message when creating first given formula:
EXTRACT has invalid inputs
If you keep having this error, can you share a screenshot of your table/column type? Thanks
Hello Aurelien,
Thank you for your reply.
The error still occured. No matter I change this virtual column to Text / Number / List.
Thank you.
You need to remove Data Table from your expression
However I am doing it with another table. I need to compose this expression with specific table(namely Data Table)
Hi @LawrenceMa
There are two steps, in two different tables.
1) In your table Data Table, you should have a virtual column "digitNumber" with this expression:
IF(STARTSWITH([5-digit Item Number], "2"),
ANY(EXTRACTNUMBERS([5-digit Item Number])),
0
)
2) in your other table, where you wish to get the max value over all the data set of Data Table, you can use this expression:
MAX(Data Table[digitNumber])
Great! It works!
Thank you so much.
Great! ๐
User | Count |
---|---|
43 | |
28 | |
23 | |
14 | |
14 |