Solved: Trim back to just the number - Page 2

Jake_Naude · 07-16-2020 03:47 PM

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

Hi @Suvrutt_Gurjar,

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

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

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

View solution in original post