topic Re: ListBox Filters Based on 'Contains' rather than 'Equal To' in QlikView

ListBox Filters Based on 'Contains' rather than 'Equal To'

Thu, 04 Sep 2014 13:53:05 GMT

I'm new to Qlikview and hopefully this is an easy question...

I would like to create a List Box that will filter the results based on whether the field 'contains' the selected item rather than being 'equal to' the selected item.

For example: using the Example Data Set and Example List Box Selections below...

If the user selects 'New' from the List Box Selections, I want to show records where the 'Category' field contains 'New'. (Would show Cutstomer1, Customer2, Customer3, and Customer7)

Example Data Set:

Customer	Category
Customer1	New;Regional;FastPay;Manual
Customer2	New;Local
Customer3	FastPay;New;Automatic;GroundOnly
Customer4	Existing;FastPay;Automatic
Customer5	Local;SlowPay;AirOnly;Type3
Customer6	Existing;Regional;
Customer7	SlowPay;GroundOnly;Manual;New

Example List Box Selections:

Category Types
AirOnly
Automatic
Existing
FastPay
GroundOnly
Local
Manual
New
Regional
SlowPay
Type3

Thanks,

John

Re: ListBox Filters Based on 'Contains' rather than 'Equal To'

MK_QSL — Thu, 04 Sep 2014 13:55:11 GMT

Load the first table as below

Load Customer, Subfield(Category,';') as Category From TableName;

Now Create a list box Category... Hope this will help..

Re: ListBox Filters Based on 'Contains' rather than 'Equal To'

Thu, 04 Sep 2014 16:32:17 GMT

Try the below script in your qvw.

temp:

LOAD
F1 as temp_Customer, F2 as Type
INLINE [
F1, F2
Customer1, New;Regional;FastPay;Manual
Customer2, New;Local
Customer3, FastPay;New;Automatic;GroundOnly
Customer4, Existing;FastPay;Automatic
Customer5, Local;SlowPay;AirOnly;Type3
Customer6, Existing;Regional;
Customer7, SlowPay;GroundOnly;Manual;New
]
;

temp1:
LOAD max(SubStringCount(Type,';'))+1 as maxlength Resident temp

;

let vmaxlength=

FieldValue('maxlength',1);

for

i=0

vmaxlength

Data:

LOAD

Distinct

temp_Customer

Customer ,

SubField(

Type,';',

$(i))

CustomerType

Resident temp;

NEXT;

Output:

NoConcatenate

LOAD

Distinct *

Resident Data

Where

len(

Trim(

CustomerType))>0;

DROP

Tables Data,temp,temp1;

May not be the best solution, but it works!!

Thanks,

Surendra