Trim back to just the number

Evening All,

Please could someone advise how to remove the coma that precedes the number one in the sample below…?

I’ve tried all kinds of trickery but am falling short!

image

I’m using this ValidIf expression in a Enum (Base Type: Number) column.

SORT(
UNIQUE(
SPLIT(
(
LIST(

SELECT(
survey[survey_branch_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))-LIST(0)

+SELECT(
survey[branch_A_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))-LIST(0)

+SELECT(
survey[branch_E_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))-LIST(0)

)
-LIST("")
),
" , "
)
)
)

Thanks… :slight_smile:

Looks like I had the -LIST("") in the wrong place… Correction shown below…

image

I’m getting a clean number now but I’m wondering how to show the 123 as separate Enum items i.e 3 buttons…?

image

SORT(
UNIQUE(
SPLIT(
(
LIST(

SELECT(
survey[survey_branch_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))

+SELECT(
survey[branch_E_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))

-LIST(0)
-LIST("")
)
),
" , "
)
)
)

These settings look right to me…

I guess my issue is related to the way I’m producing the list of numbers…?

I believe you may wish to mention the expression you are using in valid_if. Does it have an IF() function as well?

You may wish to mention specific reason you have used SPLIT() after adding those SELECT() based lists?

Alsoyou may wish to mention ,what you mean by “clean number” .I believe Valid_if should give a LIST() ?

3 Likes

Hi @Suvrutt_Gurjar,

Thanks for coming in… I’ve just this minute resolved the issue… Cheers… :slight_smile:

It was to do with the way the list was being produced… Enum list is split into separate numbers now…

Here’s what I changed:

I removed the SPLIT() and LIST() wrappers and did not remove the -LIST(0) & -LIST(""):slight_smile:

IF(
COUNT(
SELECT(
survey[survey_branch_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))
) = 0,
LIST(1),

SORT(
UNIQUE(

SELECT(
survey[survey_branch_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))

+SELECT(
survey[branch_A_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))

+SELECT(
survey[branch_B_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))

+SELECT(
survey[branch_C_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))

+SELECT(
survey[branch_D_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))

+SELECT(
survey[branch_E_number],
AND(
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs]),
([_THISROW].[select_asset_type] = [select_asset_type])
))
-LIST(0)
-LIST("")

)
)
) 

2 Likes

Great. Nice to know you figured it out.
I also was wondering why SPLIT() is there in your expression and so asked in earlier post. :slight_smile:

1 Like

Thanks… I’m not quite sure either… But this little picnic trip has taught me a few more very useful things… :slight_smile:

1 Like