topic Re: search string in field in App Development

search string in field

paulwalker — Tue, 25 Apr 2023 12:30:46 GMT

Hi Community,

I have below table - I have to compare Name and SearchName columns. (SearchName field has pipeline | separated text)

My scenario, I have to create flag based on compare between Name and SearchName. (IF Name field is available in SearchName field should flag 1 else 0)

below table is Flag is my output column

Thanks in Advance!

Re: search string in field

sergio0592 — Tue, 25 Apr 2023 13:12:20 GMT

Hi,

You can achieve this whith creating three expressions :

Field1

if(SubField([SearchName Flag],'|',1)=Name,1,0)

Field2

if(SubField([SearchName Flag],'|',2)=Name,1,0)

Field3

if(SubField([SearchName Flag],'|',3)=Name,1,0)

And the final flag FL

rangesum(Field1,Field2,Field3)

Re: search string in field

paulwalker — Tue, 25 Apr 2023 13:23:23 GMT

This should be create backend.

but this solutions is not correct I think, let say if I have 10 different text with delimiter - I have to write 10 expressions.

Re: search string in field

Sabrina_V — Tue, 25 Apr 2023 13:25:09 GMT

Hello

try this :

NoConcatenate
TMP:
Load
*
,SubStringCount (SearchName,Name) as Flag
Resident
TEST;

DROP TABLE TEST;

Here is the result :

Re: search string in field

Qrishna — Tue, 25 Apr 2023 13:39:04 GMT

A small change to @Sabrina_V code.

As substringcount returns the number times the substring exists in a field value,

NoConcatenate
TMP:
Load
*
, if(SubStringCount (SearchName,Name)<>0, 1, 0 )as Flag //tweak in the code here
Resident
TEST;

DROP TABLE TEST;

Re: search string in field

Kushal_Chawda — Tue, 25 Apr 2023 14:45:06 GMT

@paulwalker simplest way will be below

Re: search string in field

rwunderlich — Wed, 26 Apr 2023 00:55:26 GMT

@Kushal_Chawda example is simple. But if there is any chance that you would have an incorrect partial match in your data, like name "IN", then a more robust approach that uses the delimiter might be:

D2:
Left Join (Data)
LOAD
-Min(Name = AName) as Flag,
RecId
Group By RecId
;
LOAD *, SubField(SearchName, '|') as AName
Resident Data
;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: search string in field

Kushal_Chawda — Wed, 26 Apr 2023 09:23:56 GMT

@rwunderlich indeed.

Re: search string in field

MarcoWedel — Wed, 26 Apr 2023 19:44:14 GMT

Other methods to avoid false positives might be:

hope this helps

Marco

Re: search string in field

Chanty4u — Thu, 27 Apr 2023 17:49:03 GMT

May be try this

LOAD

Name,

SearchName,

If(WildMatch(SearchName, '*' & Name & '*'), 1, 0) as Flag

FROM [your_data_source_here];