9 Replies Latest reply on Sep 16, 2018 6:33 PM by Joe Oppelt

    Multiple Value Search with comma separator from a SINGLE FIELD

    najiat.chowdhury

      I have a field that contains diagnosis codes for some respective cpt codes like:
      for example:

      diagnosis codes                            cpt

      A011                                                209875
      A024, J002                                      9899

      B033                                               10897

      BS4507                                           97654

      J002                                                44889

      SP4J29, BS4507                            10067

       

       

      I need a search field where I can search for the value(s). I can search for a single value or multiple values with comma in the same search field. How can I do that?

       

      I have created a parameter named "code search" and set the data type "string" and allowable values "all".

      Then created a calculated field "code match" with the following logic:

       

      (

      [code search]=""

      )

      OR

      (

      [code search] <> ""

      and

      contains([diagnosis codes], [code search])

      )

       

      The problem with this code is, it allows me to search for one value at a time. I cannot search multiple values with comma separator.

      Please help me how to search multiple values with comma separator. And moreover, the search has to work without any alphabetical order boundary which means if I search this way J002, A011. It should give me the cpt codes for everyone where A011 and J002 is mentioned.

       

      I cannot use REGEX functions because my organization tableau version does not support them.

        • 1. Re: Multiple Value Search with comma separator from a SINGLE FIELD
          Joe Oppelt

          How many comma-separated tokens can you have on the input line?  If a fixed maximum, I can think of ways to do it.  If unlimited number, though, I would change the shape of the data to have separate rows for each diagnosis code.

          • 2. Re: Multiple Value Search with comma separator from a SINGLE FIELD
            najiat.chowdhury

            Max 26 comma separated values can be searched at a time (client requirement).

            • 3. Re: Multiple Value Search with comma separator from a SINGLE FIELD
              Joe Oppelt

              OK.  It would be tedious, but you could break the input parameter with up to 26 tokens into 26 separate calcs.  The your larger calc would look like this:

               

              (

              [code search]=""

              )

              OR

              (

              [code search] <> ""

              and

              (

              contains([diagnosis codes], [code search1]

              or

              contains([diagnosis codes], [code search2]

              or

              ...

              contains([diagnosis codes], [code search26]

              )

              )

               

               

               

              You would break the parameter apart with string functions.

               

               

              String Functions

               

               

               

              For instance, there is a FINDNTH function that lets you specify the Nth occurrence of a string.  So the second code search calc would want to grab the characters between the first and second comma.  (Or the first comma and the end of the string if there is no second comma.)  You may want to peel off leading and trailing spaces.  Stuff like that.  You'd have to play with it and test it out.  Once you get the second calc correct, then the same logic would be adapted to 3 through 26 pretty easily.

              • 4. Re: Multiple Value Search with comma separator from a SINGLE FIELD
                najiat.chowdhury

                I think I did not explain my problem perfectly. To be very precise, I have 81 diagnosis codes. Each CPT can contain a single diagnosis code or even multiple codes. Like, CPT 9098 can contain A011, BS44 diagnosis codes, where CPT 9091 might contain just A011. So when I search A011, it should return both CPT codes 9091 and 9098 (all of the cpts that contain that particular diagnosis codes).

                 

                And out of 81 diagnosis codes, I can search upto 26 codes at a time. Or I might search 1 code as well in that same search field. And if I put the search field blank (put no values to search), it should show all the cpts with 81 codes.

                 

                So, in my search box, I might search for one code, or multiple codes (highest 26 no of codes out of 81 at a time) or I might keep the search box blank to see all of the values.

                • 5. Re: Multiple Value Search with comma separator from a SINGLE FIELD
                  Joe Oppelt

                  Yes.  And that's what I expect my suggestion to do.


                  You make 26 little calcs.  And you have a parameter called [Code Search].  (Note:  I think you cannot put a null in a parameter, so you would want the user to put "All" or "all" or "ALL" rather than leaving it blank.)

                   

                  If the user enters A024, J002, then [Code Search1] would end up with a value of A024, and [Code Search2] would end up with J002, and the other 24 would be null.

                   

                  The larger calc I suggested would find CPT 9899 and 44889 because they both contain J002.

                  • 6. Re: Multiple Value Search with comma separator from a SINGLE FIELD
                    najiat.chowdhury

                    I understand you are suggesting to create 26 little calcs but I am confused

                    what are you referring to put in the little calculation fields. Can you

                    just specify the logic for one field?

                    And as I have 81 diag codes, will the user be randomly able to search any

                    26 among them?

                     

                    On Thu, Sep 13, 2018, 4:01 AM Joe Oppelt <tableaucommunity@tableau.com>

                    • 7. Re: Multiple Value Search with comma separator from a SINGLE FIELD
                      Joe Oppelt

                      If the user enters A024, J002,B033,Z777 then the first little calc ( [code search1] ) will have A024 in it, and the second will have J002 in it, and the third will have B033 in it, and the 4th will have Z7777.  The remaining 22 will be null.

                       

                      All 26 will be used in the calc I proposed earlier (in the red section.)  The null ones just won't do anything because they are null.

                       

                      What would a calc look like?  (Keep in mind, I'm typing this off the top of my head, so some syntax debugging might be necessary.)

                       

                      This would be the calc for the 4th token:

                       

                      IF FINDNTH([input parameter], "," , 3) = 0 then ""  // if zero then there is no 3rd comma, therefore no 4th token, so set to null

                      ELSE

                          IF FINDNTH( [input parameter], "," , 4) = 0  then  // if zero, then no 4th comma.  Grab from 3rd comma to the end.

                              MID( [input parameter], FINDNTH([input parameter], "," , 3)+1 )  // grab from first position after 3rd comma to the end of the string.

                              ELSE

                              MID( [input parameter], FINDNTH([input parameter], "," , 3)+1, FINDNTH([input parameter], "," , 4)-1  )

                                                                                                              // Grab from first position after 3rd comma to the position before the 4th comma

                          END

                      END

                       

                      And if the string could have spaces before or after the actual token, you want to trim off the spaces using LTRIM and RTRIM

                       

                       

                      LTRIM(RTRIM(

                      IF FINDNTH([input parameter], "," , 3) = 0 then ""  // if zero then there is no 3rd comma, therefore no 4th token, so set to null

                      ELSE

                          IF FINDNTH( [input parameter], "," , 4) = 0  then  // if zero, then no 4th comma.  Grab from 3rd comma to the end.

                              MID( [input parameter], FINDNTH([input parameter], "," , 3)+1 )  // grab from first position after 3rd comma to the end of the string.

                              ELSE

                              MID( [input parameter], FINDNTH([input parameter], "," , 3)+1, FINDNTH([input parameter], "," , 4)-1  )

                                                                                                              // Grab from first position after 3rd comma to the position before the 4th comma

                          END

                      END

                      ))

                      • 8. Re: Multiple Value Search with comma separator from a SINGLE FIELD
                        najiat.chowdhury

                        Hey Joe,

                         

                        Unfortunately, my version of data source doesn't support FINDNTH function.

                         

                        See the snip I attached.

                         

                        Any other way to achieve my goal? It's really urgent

                        findnth.PNGf_1.PNG

                        • 9. Re: Multiple Value Search with comma separator from a SINGLE FIELD
                          Joe Oppelt

                          I guess you'd have to keep another series of little calcs that store the locations of the commas.

                           

                          This would be the calc to find Comma #3:

                           

                          IF [Comma location #2]<>0 THEN    // We had a second comma, so look for the next

                          FIND([input parameter], ",", [Comma location #2]+1

                          ELSE 0 END

                           

                          You'll need one of these for every possible comma.

                           

                          And here is what token #4 looks like now:

                           

                           

                          IF [Comma location #3] = 0 then ""  // if zero then there is no 3rd comma, therefore no 4th token, so set to null

                          ELSE

                              IF [Comma location #4] = 0  then  // if zero, then no 4th comma.  Grab from 3rd comma to the end.

                                  MID( [input parameter], [Comma location #3]+1 )  // grab from first position after 3rd comma to the end of the string.

                                  ELSE

                                  MID( [input parameter], [Comma location #3]+1, [Comma location #4]-1  )

                                                                                                                  // Grab from first position after 3rd comma to the position before the 4th comma

                              END

                          END