topic Re: Qlikview Search from List Box in QlikView

Qlikview Search from List Box

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Greetings

I have field named DESCRIPTION (Long sentence)

The field will be typically looks like as follows

Example 1:

HS 25 , 5% MARGIN , LONG PATTERN EMBEDDED , PACKED IN 2KGS

Example 2:

HS-25, 15% Margin, LONG, Packed in 25KGS

EXAMPLE 3:

HS25, 20% MARGIN, PACKED in 10KGS

EXAMPLE 4:

LONG PATTERN, PACKED in 50KGS

EXAMPLE 5:

PLAIN WHITE, PACKED 20KGS

EXAMPLE 6:

BROWN FINISH, PACKED 10KGS

The Key words are HS 25, HS-25, HS25 and LONG. I need to find all the fields in table which matches the KEY Words. Example 5 and 6 is not matching.

How do I implement?

Thanks

Re: Qlikview Search from List Box

Anil_Babu_Samineni — Mon, 26 Jun 2017 01:01:48 GMT

Check there? Search ‒ QlikView

Re: Qlikview Search from List Box

Anonymous — Mon, 26 Jun 2017 01:25:19 GMT

Thanks but it does not say I can search OR

Example I need to find values

'HS 25' OR 'HS-25' OR 'HS25' OR 'LONG'

Re: Qlikview Search from List Box

Anil_Babu_Samineni — Mon, 26 Jun 2017 01:36:32 GMT

Have you enable any search on your settings. This will work normally but Qlik always prefer double quotes. If you have * symbol delete that and use like below

"HS 25" OR "HS-25" OR "HS25" OR "LONG"

Re: Qlikview Search from List Box

Anonymous — Mon, 26 Jun 2017 03:32:24 GMT

Thanks You mean associate Search?

My list box is more than 10000 fields. So while searching "HS 25" OR "HS-25" OR "HS25" OR "LONG"

How to select All?

Re: Qlikview Search from List Box

Anil_Babu_Samineni — Mon, 26 Jun 2017 04:56:32 GMT

No need to select Manually, If your filter correct just hit "Enter" it will associate to each and will display the number of values

Re: Qlikview Search from List Box

Anonymous — Mon, 26 Jun 2017 05:12:39 GMT

Thanks Anil Babu. Appreciate. I think My version do not work well. I am using version 12.0.20200.0 SR3

While entering "HS25" OR "HS-25" OR "HS 25" I can see new UI "Filter Search" by on the side.

But upon clicking enter , Filter Search by dialog goes and entire contents of list box is selected (not from my search specific).It may be some bug.

So best option is I should load using Load statement

Here is my Load Statement

LOAD

FILED1,

FILED2,

FIELD3,

DESC,

FIELDX

FROM

(ooxml, embedded labels);

I need to add a statement as follows

Where DESC="HS25" OR DESC="HS 25" OR DESC="HS-25" OR DESC="LONG"

Where should I insert above the statement? Is my statement correct?

Thanks

Re: Qlikview Search from List Box

Anil_Babu_Samineni — Mon, 26 Jun 2017 05:20:23 GMT

Here you go

LOAD

FILED1,

FILED2,

FIELD3,

DESC,

FIELDX

FROM

(ooxml, embedded labels) where Match(DESC, "HS25", "HS 25" , "HS-25" ,"LONG"); // Or WildMatch(DESC, "HS*", "LONG") -- second condition works where starting DESC string is HS

Finally, You can do same you mentioned

LOAD

FILED1,

FILED2,

FIELD3,

DESC,

FIELDX

FROM

(ooxml, embedded labels) Where DESC="HS25" OR DESC="HS 25" OR DESC="HS-25" OR DESC="LONG";

Note - Please don't create duplicate threads Re: How to click & Select from Qlikview List box Search findings

Re: Qlikview Search from List Box

Anonymous — Mon, 26 Jun 2017 05:40:55 GMT

I have tried with one field.

LOAD

FILED1,

FILED2,

FIELD3,

DESC,

FIELDX

FROM

(ooxml, embedded labels)where DESC="HS25";

It says Script error Field Not found - <HS25>

Re: Qlikview Search from List Box

eliran — Mon, 26 Jun 2017 05:49:28 GMT

Hi Ram,

In qlikview, when you want to address a string in the ETL, you should use ' and not ".

So your syntax should look like this:

LOAD

FILED1,

FILED2,

FIELD3,

DESC,

FIELDX

FROM

(ooxml, embedded labels)where DESC='HS25';

Regards,

Eliran.

Re: Qlikview Search from List Box

Anil_Babu_Samineni — Mon, 26 Jun 2017 05:49:34 GMT

Because, Qlik script consider as field when we use like below "". You must use some thing like below

LOAD

FILED1,

FILED2,

FIELD3,

DESC,

FIELDX

FROM

(ooxml, embedded labels)where DESC= 'HS25'; // Changed to Single Quote from Double quote

Re: Qlikview Search from List Box

Anonymous — Mon, 26 Jun 2017 06:22:47 GMT

Thanks. Since I am searching from the sentence for 'HS25' or 'HS 25' or 'HS-25' or 'LONG' , should it be changed something like where DESC like '%HS25%' (This too not working )

Because When I changed to where DESC ='HS25' the script executes but 0 data found.

Re: Qlikview Search from List Box

Anil_Babu_Samineni — Mon, 26 Jun 2017 06:29:53 GMT

I wonder, How qlik behaves going on in your application? will you able to provide sample application to check? I am sure, your application may secured from client prospect. So then we can freely ask Mock data for UAT? or you may doesn't have HS25 value for DESC ??

Re: Qlikview Search from List Box

Anonymous — Mon, 26 Jun 2017 07:27:14 GMT

Thanks Mr. Anil Babu

Appreciate all your help

I have some dummy data for reference https://goo.gl/vU6zDe

Here is the Load script

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='D/M/YYYY';

SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-SG';

LOAD active,

purchase_ok,

rental,

sale_ok,

standard_price,

sale_delay,

ean13,

default_code,

produce_delay,

mes_type,

DESC,

procure_method,

type,

list_price,

supply_method,

[Supplier/Supplier],

[Supplier/Supplier Product Code],

[Supplier/Supplier Product Name],

[Supplier/Minimal Quantity],

[Supplier/Delivery Lead Time],

[Expense Account/Name],

[Income Account/Name],

[Supplier Taxes/Tax Name],

[Customer Taxes/Tax Name]

FROM

(ooxml, embedded labels) where Match(DESC, 'HS25', 'HS 25' , 'HS-25' ,'LONG');

Once again thanks a lot for your help.

Re: Qlikview Search from List Box

Anil_Babu_Samineni — Mon, 26 Jun 2017 07:36:55 GMT

In your data the values are not separate values. Can you try like below

Load * from Table Where WildMatch(DESC, '*HS*','*LONG*');

Re: Qlikview Search from List Box

Anonymous — Mon, 26 Jun 2017 08:17:14 GMT

Thanks Anil babu. Your suggestion work.

Re: Qlikview Search from List Box

Anil_Babu_Samineni — Mon, 26 Jun 2017 08:27:20 GMT

Good, Can you close this thread by flag correct answer

Re: Qlikview Search from List Box

Anonymous — Mon, 26 Jun 2017 08:45:58 GMT

In addition to above If I need to exclude certain strings in Load statement is it possible

Suppose I want to exclude fields contains words like '*PR-32*', '*SHORT*' in the above load statement.. How should I rewrite?

Re: Qlikview Search from List Box

Anil_Babu_Samineni — Mon, 26 Jun 2017 08:49:31 GMT

This?

Load * from Table Where Not WildMatch(DESC,'*PR-32*','*SHORT*');

Re: Qlikview Search from List Box

Anonymous — Mon, 26 Jun 2017 08:56:40 GMT

This require two load for the same table.

But I want to achieve both. i.e INCLUDE '*HS25*' and EXCLUDE '*PR-32*'