topic Re: Find In Field in QlikView

Find In Field

Mon, 23 Jul 2012 15:33:41 GMT

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

Mon, 23 Jul 2012 16:11:02 GMT

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

swuehl — Mon, 23 Jul 2012 16:53:40 GMT

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

Re: Find In Field

Mon, 23 Jul 2012 18:17:23 GMT

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