QlikSense expression to find similar values (not exactly matching) in a table

[SR2]

Hello Talented people,

I am wondering if there a feature in QS to find similar values in a table.

Example - if a dimension has following values then can I  identify / highlight first 2 rows as the strings are exactly matching except for the last one character.

Apple_2020_1
Apple_2020_2
Mango2020_f1
Mango2021_p
Mytextabc
11textxyz

PS - I do not know the pattern that I want to match as the values are quite diverse. I just want to identify values which have an exact matching value present in this dimension except for last 1 or 2 characters in them.

Many Thanks,

Sumeer

[Taoufiq_Zarra]

@SR2 

from this input

Apple_2020_1
Apple_2020_2
Mango2020_f1
Mango2021_p
Mytextabc
11textxyz

can you also share the expected output ?

[SR2]

Hi Toufiq,

I expect first 2 rows i.e. following in output -

Apple_2020_1
Apple_2020_2

Regards,

Sumeer

[Taoufiq_Zarra]

@SR2  One option in load Script you can add  for example

Data:
load *,left(trim(Field),len(trim(Field))-1) as Temp1,left(trim(Field),len(trim(Field))-2) as Temp2 inline [
Field
Apple_2020_1
Apple_2020_2
Mango2020_f1
Mango2021_p
Mytextabc
11textxyz
];
left join load count(Temp1) as CountTmp1,Temp1 resident Data group by Temp1;

left join load count(Temp2) as CountTmp2,Temp2 resident Data group by Temp2;

output:

load Field,if(CountTmp1>1 or CountTmp2>1,1,0) as Flag resident Data;

drop table Data;

and then use Flag=1 for duplicate value except for last 1 or 2 

like :

or in dimension if(Flag=1,Field) ...

[SR2 · Accepted solution]

Thank you Taoufiq. This is helpful. 

Best Regards,

Sumeer