topic Re: Create Filter using words of certain Field in QlikView

Create Filter using words of certain Field

Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I need to find a way to create a Filter that contains certain words of a determinate Field.

For example: I have this list box of the field "Model"

I want to create some filters that filter the data depending on the words:

One that contains "Bom" or Excelente"
One that contains the brands as "Blackberry", "Samsung",etc.
One that contains the models "iPhone 5, "Z30", etc.

I would be very grateful if someone help me.

Thanks in advance.

Martín

Re: Create Filter using words of certain Field

gopikrishnach — Tue, 18 Oct 2016 17:06:04 GMT

Martin,

Can you please provide the clear screenshot of this example as the provided One is not attached properly.

You can use search option in the properties by using *bom* like that.

Gopi.

Re: Create Filter using words of certain Field

sunny_talwar — Tue, 18 Oct 2016 17:07:24 GMT

The image seems to be broken for me, would you be able to repost the image to see what you are looking to get?

Re: Create Filter using words of certain Field

Tue, 18 Oct 2016 18:56:47 GMT

Hi Gopi,

Here's the image:

Re: Create Filter using words of certain Field

Tue, 18 Oct 2016 18:57:18 GMT

Hi Sunny,

Here's the image:

Re: Create Filter using words of certain Field

rupamjyotidas — Tue, 18 Oct 2016 19:02:43 GMT

I am guessing this:

if(Match(yourfieldname,"Bom", Excelente")>0 ,yourfieldname) as yourfieldname

Same for others

if(Match(yourfieldname,"Blackberry", "Samsung")>0 ,yourfieldname) as yourfieldname

Re: Create Filter using words of certain Field

rupamjyotidas — Tue, 18 Oct 2016 19:16:35 GMT

Is the words consistent? First part seems to be the Brand Name but again iPad2 is not a brand name. You can use strings functions to break the field like subfield() but you need to be careful of the consistency of the data

Re: Create Filter using words of certain Field

Tue, 18 Oct 2016 19:17:03 GMT

I want to finally have something like this that work as list box to filter the results:

The Condition is not a Field. Bom and Excelent are just words inside the Field "Model".

I want to find a way to create that kind of list box which filter that words in the results without modifications in the original file.

Re: Create Filter using words of certain Field

Tue, 18 Oct 2016 19:17:28 GMT

I want to finally have something like this that work as list box to filter the results:

The Condition is not a Field. Bom and Excelent are just words inside the Field "Model".

I want to find a way to create that kind of list box which filter that words in the results without modifications in the original file.

Re: Create Filter using words of certain Field

Tue, 18 Oct 2016 19:17:51 GMT

I want to finally have something like this that work as list box to filter the results:

The Condition is not a Field. Bom and Excelent are just words inside the Field "Model".

I want to find a way to create that kind of list box which filter that words in the results without modifications in the original file.

Re: Create Filter using words of certain Field

sergio0592 — Tue, 18 Oct 2016 19:19:59 GMT

Or this :

=if(Fieldsname like '*iPhone 5*' or Fieldsname like "*Z30*" ,Fieldsname,null())

Re: Create Filter using words of certain Field

Tue, 18 Oct 2016 19:21:29 GMT

Yes, are consistent. For example, all the models have attributed a condition, "Bom" or "Excelent".

There is any way to do the last picture? That list box filter with the condition.

Re: Create Filter using words of certain Field

Anonymous — Tue, 18 Oct 2016 19:27:55 GMT

See if this is:

Find Key Words and Phrases in Free Text Fields

Re: Create Filter using words of certain Field

Tue, 18 Oct 2016 19:38:12 GMT

Thanks Marcio, but no.

What I need to create is this (I made it with paint):

Re: Create Filter using words of certain Field

Anonymous — Tue, 18 Oct 2016 19:54:39 GMT

These conditions are fixed?

If so, you can create a InLine with the conditions, and make the search as the item sent.

[Conditions]:

LOAD * INLINE [

Condition

'Bom'

'Excelent'

];

Re: Create Filter using words of certain Field

Tue, 18 Oct 2016 20:01:32 GMT

Marcio,

Thanks to that I managed to create the Field "Conditions" and the Listbox but when I clicked in some condition is not working as a filter of the results, I clicked and nothing happens.

Re: Create Filter using words of certain Field

Anonymous — Tue, 18 Oct 2016 21:15:36 GMT

Try this j j:

Re: Create Filter using words of certain Field

Tue, 18 Oct 2016 21:25:50 GMT

Marcio,

Works great, thanks!

The only problem is:

In LOAD * Inline [

Name

Have I to list all the devices? Because I have more than 1500...

Re: Create Filter using words of certain Field

Anil_Babu_Samineni — Wed, 19 Oct 2016 01:42:42 GMT

Do You mean this??

Script

LOAD Field, SubField(Field, ' ', -1) as Field1;

LOAD * Inline [

Field

Blackberry 9900 Seminovo Bom

Blackberry 9900 Seminovo Excelente

Blackberry 9900 Seminovo Muito Bom

Blackberry Q10 Preto Seminovo Bom

Blackberry Q10 Preto Seminovo Excelente

Blackberry Q10 Preto Seminovo Muito Bom

ipad 2 Wi-Fi + 3G 16GB Branco Seminovo Bom

ipad 2 Wi-Fi + 3G 16GB Branco Seminovo Excelente

ipad 2 Wi-Fi + 3G 16GB Branco Seminovo Muito Bom

ipad 2 Wi-Fi + 4G 32GB Branco Seminovo Bom

ipad 2 Wi-Fi + 4G 32GB Branco Seminovo Excelente

ipad 2 Wi-Fi + 4G 32GB Branco Seminovo Muito Bom

];

For Ref, PFA. Now tell me what is the Issue? Make sure if anyone reply is useful please mark as Helpful. If you are happy that Close This Thread by Flag Correct Answer

Re: Create Filter using words of certain Field

Wed, 19 Oct 2016 03:40:51 GMT

Hi Martin

you can use below function to get desire o/p:

subfield('Blackberry 9900Seminovo Bom', ' ' ,-1)