1 Reply Latest reply on Feb 17, 2017 10:00 AM by Tom W

    Column by substring search

    Signar Jakobsen

      Hi there,

       

      I have run into a problem that I have spent way too long trying to solve.

       

      I am trying to display the number of suppliers that cover a certain postal code as a bar chart.

      Postal code as the dimension

      and COUNTD(SupplierID) as the measure.

       

      The issue is this:

       

      In our database, the postal codes are stored like this:

       

      SupplierIDLocality
      1

      UK-B,UK-CV,UK-WR,UK-HR,UK-DE,UK-NG

      2

      UK-OX,UK-HP,UK-RG

      3

      UK-AB,UK-PH,UK-DD,UK-PA,UK-ZE,UK-HS

      etc..etc..

       

      The covered postal codes are all comma separated in the same string.

       

      WHAT I NEED IS THIS:

       

      I need a new dimension (probably a calculated field of some description) with the values:

      UK-AB

      UK-AL

      UK-B

      UK-BA

      etc.

      I want it to display the values that include "UK-AB", "UK-AL" etc.. I am expecting duplicate SupplierIDs across the postal codes.

       

      I have tried using Calculated fields like

       

      IF CONTAINS([Locality],"UK-AB") = TRUE THEN "UK-AB"

      ELSEIF CONTAINS([Locality],"UK-AL") = TRUE THEN "UK-AL"

      etc..

      END

       

      But this always returns the first value it hits.

              

      UK-AB
      UK-AL
      UK-B
      UK-BA
      UK-BB

       

      Any suggestions? :-)