Forum Discussion

EdjeVis's avatar
EdjeVis
New Member
2 years ago
Solved

Set value in string variable in multiple steps

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.DescriptionKindTypeReason
SO123[Description of SO123]SportMedior 
SO124 MusicJuniorDescription is empty
SO125[Description of SO125] SeniorKind 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 ðŸ™„

  • BeaBF's avatar
    BeaBF
    2 years ago

    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

7 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    2 years ago

    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
  • EdjeVis's avatar
    EdjeVis
    New Member
    2 years ago

    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's avatar
      BeaBF
      Super User
      2 years ago

      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's avatar
        BeaBF
        Super User
        2 years ago

        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