topic Re: QlikSense expression to find similar values (not exactly matching) in a table in App Development

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

SR2 — Thu, 01 Apr 2021 13:46:08 GMT

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

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

Taoufiq_Zarra — Tue, 23 Feb 2021 09:04:01 GMT

@SR2

from this input

Apple_2020_1
Apple_2020_2
Mango2020_f1
Mango2021_p
Mytextabc
11textxyz

can you also share the expected output ?

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

SR2 — Wed, 24 Feb 2021 08:28:04 GMT

Hi Toufiq,

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

Apple_2020_1
Apple_2020_2

Regards,

Sumeer

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

Taoufiq_Zarra — Wed, 24 Feb 2021 08:43:59 GMT

@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) ...

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

SR2 — Mon, 01 Mar 2021 05:55:27 GMT

Thank you Taoufiq. This is helpful.

Best Regards,

Sumeer