Find In Field

[#1]

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??

[#2]

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.

[swuehl · Accepted solution]

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)) ) ,',')

[#4]

Thank you both for the help!!! I really appreciate it.