I have what I think is a somewhat unusual question.
Letโs say I have a list as follows:
{1, 3, 5}
What I want to do is to removed all values from the list that are above or below a certain value. So, if I removed every element of the list that is less than 2, my list would be as follows:
{3, 5}
I know how to do this if I have a column with with various values. However, that is not the case I am faced with today. Today, I have column in which each cell might have multiple values as follows:
1, 3, 5
Iโd like to use SPLIT() to make each cell into a list and then compare the values in the list with a value in another cell to get rid of those values that are less than it. Can that be done? If thereโs no better way, I think I might be able to combine SORT() and INDEX() in a convoluted expression that can handle a finite number of list items but Iโm wondering if thereโs a simpler way.
If you have cell with multi values like โ1 , 2 , 3โ, you sould be able to convert your list like SPLIT(CONCATENATE(Table[Column])," , "). Does this help you enough?
Thanks @Aleksi! I know about SPLIT() and I think I may be able to combine it with INDEX() to go through one by one (letโs say, up to 6 or whatever) to test each value, but I was wondering if there might be an easier way to get rid of the values in such a list that are above or below a certain value.
Hello @Kirk_Masden san
Can you always predict the max number (count) of the list items? 6 is forseeable max? Or you cant predict what is the max count of list items?
If you can predict or restrict max count, then I have an idea.
How aboutโฆ
INTERSECT(
SPLIT(CONCATENATE(Table[EnumListColumn])," , โ),
SPLIT(CONCATENATE(LIST(6,7,8,9,10)),โ , "))
I think about like thisโฆ
Your values are from 1 to 10 and you need to check what numbers are more than 5 (what numbers belong to intersect 6,7,8,9,10). Sampleโฆ if the column contains 1,3,5,7,9โฆ result is 7,9. Another idea with this formula is that it doesnโt need to be below or above.
UNTESTED
To get all values less than split-value:
(
SPLIT(
INDEX(
SPLIT(
SORT(
[list-column] // existing list
+ {split-value} // list containing only split-value
+ LIST("") // minimum value to ensure item before split-value
+ {beyond-max} // maximum value to ensure item after split-value
- LIST() // remove duplicates
),
concatenate(
" , ", // guaranteed by blank added above
min-value,
" , " // guaranteed by beyond-max added above
)
),
1 // 1 for values less than split-value, 2 for more than
),
" , "
)
- LIST("", beyond-max) // remove blank, beyond-max added above
)
Assumptions:
[list-column]
contains no blank values or blank removal is okay.
[list-column]
contains no duplicate values or duplicate removal is okay.
beyond-max is a value greater than might ever occur in in [list-column]
.
Remove all //
comments to use the expression.
At this point, Iโve solved the problem using Steveโs approach. Iโm still studying the other approaches and they may be equally good, but Iโve confirmed that the direction @Steve pointed me in worked out.
Hereโs my understanding of Steveโs approach:
Thatโs a bit of an oversimplification but thatโs pretty much what I have done following Steveโs lead.
I can report, however, that the following was rejected by AppSheet:
+ {""}
This produced the following error:
{""} could not be parsed due to exception: Sequence contains no elements.
Also, since I have many values over 1000 I found it necessary to use concatenate() several times to get rid of commas between the hundreds and thousands places. I found that if I had some values with the commas and some without that SORT() didnโt work properly. Also, the commas interfered with proper functioning of SPLIT(), as I recall.
Iโm fascinated and amazed by the many different approaches that have been so kindly suggested. I would like to continue to study all of them but for now, @Steveโs approach has done the trick for me. Thanks everyone!
Exactly correct!
Grrrโฆ Try + LIST("")
instead. Probably also should replace - {"", beyond-max}
with - LIST("", beyond-max)
, too. Iโve updated my earlier post to reflect these fixes.
Thanks so much everyone!! I need to study these solutions. I deeply appreciate the extremely helpful feedback and would like to report back after I learn to use them.
@tsuji_koichi I think the total number of list items may be six or so. So, I think that using index() I could go through each one up to that number. I need to study how @Steve has used INDEX() but it looks like his approach is different from what I had imagined. I donโt understand @Aleksiโs use of INTERSECT() yet but I think thatโs probably just my own failure to wrap my brain around it. Iโll work on it.
As far as you can place the max number of count for the enumlist items, then it could work.
Not sure your exact use case in terms of how to generate the enumlist, which comprise of the numeric number.
See screenshot.
Is this kinda of what you like to achieve?
Thanks a lot! What is the expression in the background?
To get started with, I just unhided the hidden fileds for the explanation.
Base Number is number type where the app user enter the arbtray number for the subscruction calc, i.e. N - each enum list item.
Count is just count the number of items for the list.
Base list. This is a bit of trick.
I made independent table looks like this.
Base list filed is ref type field, which refer to this table based on โCountโ Fields. By doing so, we are able to generate the list such as
{1}
{1 , 1}
{1 , 1, 1} โฆ based on the count of list items dynamically.
Then Base number dynamic list.
For this quick sample, I retrcted the max number of count = 3. The user can not select 4 and more enum list items.
App formula for this field
IFS(
[Count]=1,
list(number(index([Base List],1))*[Base Number]),
[Count]=2,
list(number(index([Base List],1))*number([Base Number]),number(index([Base List],2))*number([Base Number])),
[Count]=3,
list(number(index([Base List],1))*number([Base Number]),number(index([Base List],2))*number([Base Number]),,number(index([Base List],3))*number([Base Number]))
)
Looks complex, but not so. I think this expression self explanatory. Based on count of list and the arbitray number for the substruct calc, it will generate the dymamic list.
Lastly the result.
IFS(
[Count]=1,
list(
IF(
number(index([Base Number Dymamic List],1))-number(index([Enum List],1))>0,
number(index([Base Number Dymamic List],1))-number(index([Enum List],1)),
" "
)
)-list(" ")
,
[Count]=2,
list(
IF(
number(index([Base Number Dymamic List],1))-number(index([Enum List],1))>0,
number(index([Base Number Dymamic List],1))-number(index([Enum List],1)),
" "
),
IF(
number(index([Base Number Dymamic List],2))-number(index([Enum List],2))>0,
number(index([Base Number Dymamic List],2))-number(index([Enum List],2)),
" "
)
)-list(" "),
[Count]=3,
list(
IF(
number(index([Base Number Dymamic List],1))-number(index([Enum List],1))>0,
number(index([Base Number Dymamic List],1))-number(index([Enum List],1)),
" "
),
IF(
number(index([Base Number Dymamic List],2))-number(index([Enum List],2))>0,
number(index([Base Number Dymamic List],2))-number(index([Enum List],2)),
" "
),
IF(
number(index([Base Number Dymamic List],3))-number(index([Enum List],3))>0,
number(index([Base Number Dymamic List],3))-number(index([Enum List],3)),
" "
)
)-list(" ")
)
If the permissible โMAXโ count of the list is 6, then amend the calc accordingly.
In case we are not able to put a bar for max permissible count for the list, I suppose we need to employ the Steveโs โLooping Actionsโ technique. But by the number of the count of list foreseeable always, we can push all the possible different case within the calculation, which make it possible to run the calc dynamically.
Hope a bit of help for you.
I have tested with a few numbers as below picture, based on my understanding on this. As comparison I have also indicates of using google sheet formula:
TOP(
SORT([EnumList],TRUE),
IFS(
INDEX(SORT([EnumList],TRUE),6)>=[MinValue],6,
INDEX(SORT([EnumList],TRUE),5)>=[MinValue],5,
INDEX(SORT([EnumList],TRUE),4)>=[MinValue],4,
INDEX(SORT([EnumList],TRUE),3)>=[MinValue],3,
INDEX(SORT([EnumList],TRUE),2)>=[MinValue],2,
INDEX(SORT([EnumList],TRUE),1)>=[MinValue],1,
)1
)
TEXTJOIN(", ", 1, FILTER((TRANSPOSE(SPLIT(A1,","))), (TRANSPOSE(SPLIT(A2, ",")))>=B2))
Thank you so much, @Heru! Iโve been a bit busy and have not yet had enough time to study your answer and the other answers carefully enough. Iโll study and experiment some more and get back to this thread.
User | Count |
---|---|
41 | |
36 | |
28 | |
23 | |
16 |