There are two tables having several identical columns. In the first table, the columns have text values, in the second, yes / no values for specifying combinations.
I need to make a formula so that a string is obtained only from the values of the columns of the first table for which it is yes in the second one.
I know how to concatenate the values into a string, but I can’t get rid of the extra delimiters. Please help!
I made a sample that will show you what the problem is.
I did it using the Filter, but maybe there is a better solution?
Join("-",List( IF(thisRow.[Column 2],[Values 2].[Column 2] ,""), IF(thisRow.[Column 3],[Values 2].[Column 3] ,""), IF(thisRow.[Column 4],[Values 2].[Column 4] ,""), IF(thisRow.[Column 5],[Values 2].[Column 5] ,"")).Filter(CurrentValue!=""))
Hi @Nurlan_A ,
Happy you found out yourself a solution!
I don’t know if this can fit your use case but I’d model data in a different way so that you don’t have to rely on static formula references.
Have a look a this example:
(Please, don’t consider the button for data generation, they are just a convenient way to fill up tables properly).
By building the tables this way you can “mimic” a matrix (by grouping both horizontally and vertically), but still have the dynamic reference of rows; therefore adding values, will be reflected in the combination generation.
Let me know if this helps.
Thank you for taking your time and suggesting such an interesting solution. Unfortunately, this does not quite suit me because of the design and complexity, but I think that someday it will come in handy.