topic Re: Creating listbox with SQL Like Statement in QlikView

Creating listbox with SQL Like Statement

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

Experts,

Currently my scripts as follows

LOAD No,

     PID,

     SDATE,

     TCCODE,

     DESCRIPTION,

     QUANTITY,

     UOM,

     RATE,

     CURRENCY,

     PRICE,

     REPRESENTATIVE

     FROM

(ooxml, embedded labels, table is APR2016 )where DESCRIPTION like '*32*';

When I analyse the field values I have values NODE 44232, DAILY BC 32, MONTHLY BC32, MONTHLY BC-32 etc

I need to query BC 32, BC32, BC-32 only. Like '*32*' is not suitable.

Also instead of putting the statement in Load, I would like to have a listbox (Expression) with a Single SQL Like statement where user can select.

What is the expression I should use in Listbox?

Thanks

Re: Creating listbox with SQL Like Statement

rwunderlich — Sun, 04 Sep 2016 06:03:29 GMT

=Aggr(if(Match(TCCODE, 'BC 32', 'BC32', 'BC-32'),TCCODE), TCCODE)

-Rob

Re: Creating listbox with SQL Like Statement

ecolomer — Sun, 04 Sep 2016 19:18:29 GMT

Try

(ooxml, embedded labels, table is APR2016 )where DESCRIPTION like '*32*' and DESCRIPTION like '*BC*';

Re: Creating listbox with SQL Like Statement

MarcoWedel — Sun, 04 Sep 2016 21:53:52 GMT

or maybe

where DESCRIPTION like '*BC*32*';

regards

Marco

Re: Creating listbox with SQL Like Statement

Anonymous — Mon, 05 Sep 2016 01:27:10 GMT

Thanks Rob,

My Apologies if I have not made my request clear.

I have added a list box and in Field I should choose TCCODE or Create an Expression and Enter

=Aggr(if(Match(TCCODE, 'BC 32', 'BC32', 'BC-32'),TCCODE), TCCODE)

-Rob

I tried as follows (Expression) Contains above code you have suggested.

But What I am looking forward is as follows. A List Box lists specific keywords to search. Upon Selecting this listboxitem,, a Table shows all fields and TCCODE filed with string matches the selection will be displayed.

Re: Creating listbox with SQL Like Statement

Anonymous — Mon, 05 Sep 2016 01:27:54 GMT

Thanks Marco. I do not wish to put this in Load statement. I would prefer to have them listed in a list box. How do I do it?

Re: Creating listbox with SQL Like Statement

Anonymous — Mon, 05 Sep 2016 01:28:23 GMT

Thanks Enrique.

I do not wish to put this in Load statement. I would prefer to have them listed in a listbox. How do I do it?

Re: Creating listbox with SQL Like Statement

MarcoWedel — Mon, 05 Sep 2016 05:50:38 GMT

Where are the list box values coming from if not loaded?

Re: Creating listbox with SQL Like Statement

Peter_Cammaert — Mon, 05 Sep 2016 08:00:45 GMT

In the QlikView UI there is no real SQL-like query langiuage available, but the very powerful Search function offers you much of the same functions and advantages.

Do a search for specific wildcard values in the regular TCODE listbox, and create a bookmark with a proper name. Then create a Bookmarks object and it will show a list of those predefined searches.

Best,

Peter

Re: Creating listbox with SQL Like Statement

Anonymous — Mon, 05 Sep 2016 08:27:12 GMT

Go To list box properties -> instead of selecting Field for LB, write expression ->

If(Description like '*32*', Description)

Re: Creating listbox with SQL Like Statement

Anonymous — Mon, 05 Sep 2016 11:11:00 GMT

Hi Marco

To make clear understanding I have given 4 examples of my TCCODE. Some field may not have BC-32 or

Example 1: 'BC-32'

BC-32, PH VALUES 7.2, MOISTURE < 10%, BROKEN < 0.5%, PACKINGIN 10 KGS ONE SIDE LAMINATED BAGS. CARGO IN TRANSIT

Example 2: 'BC32'

PH VALUES 7.1, PACKAGING 10 KGS DOUBLE SIDE LAMINATED BAGS, BC32 6 COLOR PRINTING, MOISTURE < 8%, BROKEN < 0.1%,

Example 3: 'BC 32'

PH VALUES 7.0, VAPOUR %0.2, PACKAGING 5 KGS DOUBLE SIDE LAMINATED BAGS, BC 32 6 COLOR PRINTING, MOISTURE < 8%, PENDING CLEARANCE

Example 4: No 'BC 32/BC32/BC 32'

PH VALUES 7.0, VAPOUR %0.2, PACKAGING 25 KGS DOUBLE SIDE LAMINATED BAGS, 2 COLOR SINGLE SIDED PRINTING, MOISTURE < 1%, CLEARED IN TRANSIT

So I need to Select all columns which has BC 32 or BC-32 or BC32

Now temporarily I am using it in Load.

(ooxml, embedded labels, table is APR2016 where TCCODE like '*BC 32*' or TCCODE like '*BC32*' or TCCODE like '*BC-32*' ;

This I would like to change it to a Listbox like below. upon clicking the list box, the expression similar to where clause should be executed.

Hope I am not creating any confusion. Thanks

Re: Creating listbox with SQL Like Statement

swuehl — Mon, 05 Sep 2016 17:24:54 GMT

Maybe

LOAD No,

PID,

SDATE,

TCCODE,

If(Wildmatch(TCCODE,'*BC32*','*BC 32*','BC-32*'),'Find *BC32* or *BC 32* or *BC-*32') as FindTCCode,

DESCRIPTION,

QUANTITY,

UOM,

RATE,

CURRENCY,

PRICE,

REPRESENTATIVE

FROM

(ooxml, embedded labels, table is APR2016 );

Then use FindTCCode in a list box and make the selection to filter.

Re: Creating listbox with SQL Like Statement

Anonymous — Fri, 09 Sep 2016 01:58:22 GMT

Thanks Stefan.