3 Replies Latest reply: Jul 23, 2012 2:17 PM by Rebecca Molstad RSS

    Find In Field

    Rebecca Molstad

      The following values are entered into an input box (vShipmentID), which searches for the values in the field 'ShipmentID':

       

      1234

      1235

      200

       

       

      The first two values are returned, but the third value is not found in the data. Is there a way I can make my conditional text box say 'The following ShipmentID(s) were not found: 200'? If the second two values were not found, the conditional text box would need to say 'The following ShipmentID(s) were not found: 1235, 200'.

       

      I can't figure out how to write the expression, but maybe smthg like this: if Subfield(vShipmentID,'  ',1) is found in ShipmentID, return Subfield(vShipmentID,'  ',1)... etc. until all values are accounted for. However, there may be hundreds of values, so I would prefer not to use a thousand if statements.

       

      Any ideas??

        • Re: Find In Field

          Rebeccad,

           

          Try the following script:

           

          =Concat(
          if(Match(ShipmentID,SubField(vShipmentID,',',ValueLoop(1,SubStringCount(vShipmentID,',')+1)))>=1,SubField(vShipmentID,',',ValueLoop(1,SubStringCount(vShipmentID,',')+1)))
          ,',')
          

          Attached example.

           

          Kiran.

            • Re: Find In Field
              Stefan Wühl

              I think that will return the found values.

               

              If you want to stick to this approach and you want to get the 'not found' values, you can reuse code I posted here:

              http://community.qlik.com/message/162706#162706

               

               

              i.e. using these variables:

               

              vShipmentID (will be set by the INPUT box, comma separated values, no spaces)

               

              vFound:

              =Concat(

              if(Match(ShipmentID,SubField(vShipmentID,',',ValueLoop(1,SubStringCount(vShipmentID,',')+1)))>=1,SubField(vShipmentID,',',ValueLoop(1,SubStringCount(vShipmentID,',')+1)))

              ,',')

               

              vAnum:

              =SubStringCount(vShipmentID,',')+1

               

              vNotFound:

              =concat( if(wildmatch(vFound,'*'&subfield(vShipmentID,',',ValueLoop(1,vAnum,1))&'*'),NULL(),subfield(vShipmentID,',',ValueLoop(1,vAnum,1)) ) ,',')