Forum Discussion

JamesMF1982's avatar
JamesMF1982
Frequent Visitor
3 years ago
Solved

Using Search in a measure

HI all,

 

I'm hoping someone can help me. I have a column (RESIDENT[AdminType]) that has a load of varying data in there which represents a number of settings. For example "NurseLABench" that will mean Nursing,  Local Authority, benchmark or FrailPCTNegot which will mean Frail, PCT and Negotiate

 

What I want to do is search the column and return full words based on found words in the phrase. I can't use a column due to restrictions so have to use a measure.

 

I thought something like SWITCH would work with SEARCH, but I can't get anything to stick.

 

Any help would be amazing.

 

Thanks

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi JamesMF1982 
    If you are creating a measure then you may try

    Key Word =
IF (
    HASONEVALUE ( RESIDENT[AdminType] ),
    CONCATENATEX (
        VALUES ( RESIDENT[AdminType] ),
        SWITCH (
            TRUE (),
            CONTAINSSTRING ( RESIDENT[AdminType], "Nurse" ), "Nursing",
            CONTAINSSTRING ( RESIDENT[AdminType], "LA" ), "Local Authority"
        )
    )
)

4 Replies

  • TomasAndersson's avatar
    TomasAndersson
    Solution Sage
    3 years ago

    What would you like the result to be if several words exist? A measure with several full words after each other, like "Nursing Local Authority Benchmark" or something like that?

    I think SWITCH() is not perfect in this case since you want to test for each possible word and possibly return several words.

    One way (although impractical depending on the number of possible words) would be to do string several either IF(SEARCH()) or maybe even better IF(CONTAINSSTRING()) and test for each word. If the word exists you return the word string, and if not return blank. You would also decide on how to do punctuation/spacing if you have several matches.

    result = 
if(CONTAINSSTRING(RESIDENT[AdminType],"Nurse"),"Nursing ","") &
if(CONTAINSSTRING(RESIDENT[AdminType],"LA"),"Local authority ","")


    And so on. Could work.

    • JamesMF1982's avatar
      JamesMF1982
      Frequent Visitor
      3 years ago

      Hi, I have tried this but when I use the CONTAINSSTRING option, the column I am trying to reference is not available for selection.

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Hi JamesMF1982 
        If you are creating a measure then you may try

        Key Word =
IF (
    HASONEVALUE ( RESIDENT[AdminType] ),
    CONCATENATEX (
        VALUES ( RESIDENT[AdminType] ),
        SWITCH (
            TRUE (),
            CONTAINSSTRING ( RESIDENT[AdminType], "Nurse" ), "Nursing",
            CONTAINSSTRING ( RESIDENT[AdminType], "LA" ), "Local Authority"
        )
    )
)