Set value in string variable in multiple steps

[EdjeVis]

Hi,
Could someone please help me on this.

Been searching for hours on several platforms, but can't find any solution.

Challenge: I want to put a text in my column "Reason", but this text is depending on the values in different columns and (and this is the tricky part) I'd like to have a carriage return between these texts.

So example data:

No.	Description	Kind	Type	Reason
SO123	[Description of SO123]	Sport	Medior
SO124		Music	Junior	Description is empty
SO125	[Description of SO125]		Senior	Kind is empty
SO126	[Description of SO126]			Description is empty Kind is empty

Now, the problem is that I cannot just always add UNICHAR(10) after the addition of a column in the text string, because if the values in the columns are okay, there should be no comment for that column. If I would say

Reason =

  IF([Description] = "","Description is empty","") & UNICHAR(10) &

  IF([Kind] = "","Kind is empty","") & UNICHAR(10)

  IF([Type] = "","Type is empty","")

than even though all colums have a valid value, 2 carriage returns would be added.

So I'm looking for a solution and I can't see another solution than build my variable/column Reason in steps, but I can't find how.

So something like

Reason =

VAR strReason = IF([Description] = "","Description is empty","")
VAR strReason = IF(strReason = <> "", UNICHAR(10),"") & IF([Kind] = "","Kind is empty","") & UNICHAR(10)

VAR strReason = IF(strReason = <> "", UNICHAR(10),"") & IF([Type] = "","Type is empty","")

RETURN

strReason

I know the code above is not okay, but I hope I can state my problem with this 🙄

[EdjeVis]

Hi BeaBF,

Thanks for this very quick answer.

This indeed works, but now you've only checked on Description and Kind.

The trick is to check on the various combinations that are possible if you would also take Type into account.

And if I want to extend my checks, the possible combinations are increasing exponentially

[BeaBF]

@EdjeVis 

To extend the checks to include the Type column while managing multiple combinations effectively, we can use a structured approach that handles each condition based on whether each field (Description, Kind, Type) is empty or not.

Reason =

VAR DescriptionTrimmed = TRIM([Description])

VAR DescriptionCheck = IF(LEN(DescriptionTrimmed) = 0, "Description is empty", "")

VAR KindTrimmed = TRIM([Kind])

VAR KindCheck = IF(LEN(KindTrimmed) = 0, "Kind is empty", "")

VAR TypeTrimmed = TRIM([Type])

VAR TypeCheck = IF(LEN(TypeTrimmed) = 0, "Type is empty", "")

VAR CombinedReason =

    DescriptionCheck &

    IF(DescriptionCheck <> "" && (KindCheck <> "" || TypeCheck <> ""), UNICHAR(10), "") &

    KindCheck &

    IF(KindCheck <> "" && TypeCheck <> "", UNICHAR(10), "") &

    TypeCheck

RETURN

    TRIM(CombinedReason)

[BeaBF · Accepted solution]

@EdjeVis i'va also created  a more modular and scalable code:

Reason =
VAR DescriptionTrimmed = TRIM([Description])
VAR DescriptionCheck = IF(LEN(DescriptionTrimmed) = 0, "Description is empty", "")

VAR KindTrimmed = TRIM([Kind])
VAR KindCheck = IF(LEN(KindTrimmed) = 0, "Kind is empty", "")

VAR TypeTrimmed = TRIM([Type])
VAR TypeCheck = IF(LEN(TypeTrimmed) = 0, "Type is empty", "")

VAR AdditionalCheck1 = ""
VAR AdditionalCheck2 = ""
-- Add more check variables here as needed

VAR Checks = {
DescriptionCheck,
KindCheck,
TypeCheck,
AdditionalCheck1,
AdditionalCheck2
-- Add more checks here
}

VAR CombinedReason =
CONCATENATEX(
FILTER(Checks, [Value] <> ""),
[Value],
UNICHAR(10)
)

RETURN
TRIM(CombinedReason)

BBF

[BeaBF]

@EdjeVis if it's ok, please accept my answear as a solution. 

[EdjeVis]

Hi BeaBF,

Thanks, this is indeed more what I was looking for.

Since I don't mind a bit more complicated IF-statements and I love less code and variables, I would reduce it to:

VAR DescriptionCheck = IF(LEN(TRIM([Description])) = 0, "Description is empty", "")

VAR KindCheck = IF(LEN(TRIM([Kind])) = 0, "Kind is empty", "")

VAR TypeCheck = IF(LEN(TRIM([Type])) = 0, "Type is empty", "")

VAR Checks = {
DescriptionCheck,
KindCheck,
TypeCheck
}

VAR CombinedReason =
CONCATENATEX(
FILTER(Checks, [Value] <> ""),
[Value],
UNICHAR(10)
)

RETURN
TRIM(CombinedReason)

[BeaBF]

@EdjeVis Hi!

Here the calculated column:

Reason =

VAR DescriptionTrimmed = TRIM([Description])

VAR DescriptionCheck = IF(LEN(DescriptionTrimmed) = 0, "Description is empty", "")

VAR KindTrimmed = TRIM([Kind])

VAR KindCheck = IF(LEN(KindTrimmed) = 0, "Kind is empty", "")

VAR CombinedReason =

    DescriptionCheck &

    IF(DescriptionCheck <> "" && KindCheck <> "", UNICHAR(10), "") &

    KindCheck

RETURN

    TRIM(CombinedReason)

BBF

[BeaBF]

Here the screen: