search string in field

[paulwalker]

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

Name,  SearchName Flag
INDIA,  INDIA, 1
USA,  USA|Srilanka, 1
AUS,  INDIA, 0
PAK,  USA|Srilanka|NZ, 0
AUS,  INDIA|AUS|PAK, 1

Thanks in Advance!

[sergio0592]

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)

[paulwalker]

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.

[Sabrina_V]

Hello

try this : 

TEST:
Load * Inline [
Name,SearchName
INDIA,INDIA
USA,USA|Srilanka
AUS,INDIA
PAK,USA|Srilanka|NZ
AUS,INDIA|AUS|PAK
];

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

DROP TABLE TEST;

Here is the result : 

[Qrishna]

A small change to @Sabrina_V  code.

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

TEST:
Load * Inline [
Name,SearchName
INDIA,INDIA
USA,USA|Srilanka
AUS,INDIA
PAK,USA|Srilanka|NZ
AUS,INDIA|AUS|PAK
];

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

DROP TABLE TEST;

[Kushal_Chawda]

@paulwalker  simplest way will be below

Load *, if(index(SearchName,Name),1,0) as Flag
Inline [
Name,SearchName
INDIA,INDIA
USA,USA|Srilanka
AUS,INDIA
PAK,USA|Srilanka|NZ
AUS,INDIA|AUS|PAK
];

[rwunderlich]

@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:

Data:
Load *,
  RecNo() as RecId
Inline [
Name,SearchName
INDIA,INDIA
USA,USA|Srilanka
AUS,INDIA
PAK,USA|Srilanka|NZ
AUS,INDIA|AUS|PAK
];

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

[Kushal_Chawda]

@rwunderlich  indeed. 

[MarcoWedel]

Other methods to avoid false positives might be:

table1:
LOAD *,
     Sign(Index('|'&SearchName&'|','|'&Name&'|')) as Flag,
     -('|'&SearchName&'|' like '*|'&Name&'|*')    as Flag2
INLINE [
    Name, SearchName
    INDIA, INDIA
    USA, USA|Srilanka
    AUS, INDIA
    PAK, USA|Srilanka|NZ
    PAK, USA|Srilanka|PAK
    AUS, INDIA|AUS|PAK
    AUS, INDIA|AUSTRIA|NZ
    NZ,  INDIA|AUS|TANZANIA
    NZ,  INDIA|AUS|NZ
];

hope this helps

Marco

[Chanty4u]

May be try this 

LOAD 

    Name,

    SearchName,

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

FROM [your_data_source_here];