Trim back to just the number

[Jake_Naude]

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!

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…

[Jake_Naude]

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

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

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("")
)
),
" , "
)
)
)

[Jake_Naude]

Jake_Naude:

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

These settings look right to me…

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

[Suvrutt_Gurjar]

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() ?

[Jake_Naude · Accepted solution]

Hi @Suvrutt_Gurjar,

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

Jake_Naude:

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

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

Suvrutt_Gurjar:

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() ?

Here’s what I changed:

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

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("")

)
)
) 

[Suvrutt_Gurjar]

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.

[Jake_Naude]

Suvrutt_Gurjar:

I also was wondering why SPLIT() is there in your expression and so asked in earlier post.

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