topic Re: Keyword Listbox in QlikView

Keyword Listbox

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

Hi All -

I'm doing a project where I have to classify some data according to keywords found in several other fields. Below is an example of script I created in another application. I would like the words that lead off each line (Shopping, Flight, Transport......) to become a new field in my table so that it would be selectable from a list box. PRCH_HRCH_NM and MCC_DSCR are fields in my dataset which include my search words. My intention is to take this additional application out of service and have QV directly create this field.

Any help would be greatly appreciated.

Thanks!

George

'Shopping' IF FINDMULTI( PRCH_HRCH_NM, "Clothing" )

'Shopping' IF FINDMULTI( MCC_DSCR,"Pet shop","Motion","Building","jigsaw","Perfume","Propane","Fuel dealers","Drapery","Musical","Warehous","Drugs","Paint","Electrical parts","Photof" )

'Flight' IF FINDMULTI( MCC_DSCR"travel agen","Visa","ESTA" )

'Flight' IF FINDMULTI( PRCH_HRCH_NM, "Airlines" )

'Transport' IF FINDMULTI( PRCH_HRCH_NM, "Vehicle","Automobile","washes","Transp" )

'Transport' IF FINDMULTI( MCC_DSCR,"Automo","Taxi","Cabs","Bus lines","Washes","Transp")

'CCC Info' IF FINDMULTI( PRCH_HRCH_NM, "Invalid MCC" )

'Accomodation' IF FINDMULTI( PRCH_HRCH_NM, "Lodging" )

'Food/Drink' IF FINDMULTI( PRCH_HRCH_NM, "Eating","Food" )

'Health' IF FINDMULTI( PRCH_HRCH_NM, "Health service" )

'Health' IF FINDMULTI( MCC_DSCR,"dental","medical")

'Post/Gov' IF FINDMULTI( MCC_DSCR,"Postal","Courier","Freight")

'Org/Sch/College' IF FINDMULTI( MCC_DSCR,"Organi","School","College","Associa","Subscrip")

'Rec or Event' IF FINDMULTI( MCC_DSCR,"athltic","Golf","Amusement","Bands","Tourist","Caterers","Recreation","Bowling","Theatrical","Dolphin")

'Govt' IF FINDMULTI( PRCH_HRCH_NM, "Govern" )

'Miscellaneous' IF FINDMULTI( MCC_DSCR,"elsewhere")

'Cable/Tele' IF FINDMULTI( MCC_DSCR,"cable","Telecom","Phone")

'Cable/Tele' IF FINDMULTI( MCC_DSCR,"Book","Computer","Florist","Direct market")

'Miscellaneous' IF FINDMULTI( MCC_DSCR,"data prc","consulting","Information","Print","Advertis","Engineering","premiums","Preparation","Residential","Contractors")

'Miscellaneous'

Re: Keyword Listbox

pradosh_thakur — Thu, 19 Oct 2017 14:11:15 GMT

did you try the function WILDMATCH() in QV?

regards

Pradosh

Re: Keyword Listbox

gfisch13 — Thu, 19 Oct 2017 14:15:16 GMT

I have not as I'm new to the software and don't fully understand how to script this task out.

Re: Keyword Listbox

fvelascog72 — Thu, 19 Oct 2017 14:16:43 GMT

Hi,

Try with attached file.

Saludos.

Re: Keyword Listbox

felipedl — Thu, 19 Oct 2017 14:16:50 GMT

Using the attached Excel classification, you could use something like the bellow script to classify:

DataSet:

Load * Inline

[

Field,Desc

PRCH_HRCH_NM,Vehicle

PRCH_HRCH_NM,Lodging

PRCH_HRCH_NM,Clothing

MCC_DSCR,Visa

];

Tmp:

LOAD Classification,

Field,

Item

FROM

C:\Users\eferibe\Desktop\Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

left join (DataSet)

Load

Field,

Item as Desc,

Classification

Resident Tmp;

drop table Tmp;

Re: Keyword Listbox

effinty2112 — Thu, 19 Oct 2017 14:24:32 GMT

Hi George,

I pasted your text into the attached txt file then ran this script:

Lines:

Load

Distinct

Subfield(SubField(Lines,' ',1),Chr(39),2) as FieldValues;

LOAD Lines

FROM

Lines.txt

(txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);

to get:

FieldValues
Accomodation
Cable/Tele
CCC
Flight
Food/Drink
Govt
Health
Miscellaneous
Org/Sch/College
Post/Gov
Rec
Shopping
Transport

Cheers

Andrew

Re: Keyword Listbox

gfisch13 — Thu, 19 Oct 2017 14:28:39 GMT

Appreciate all your responses, what I'm not understanding is how this applies where there are multiple values in the search field. How do these procedures account for that?

Re: Keyword Listbox

effinty2112 — Thu, 19 Oct 2017 14:35:13 GMT

Hi George,

Maybe I misunderstood your need. Instead of one field you need 13 fields like for example

Flight

Visa

EST

Airlines

Is that right?

Andrew

Re: Keyword Listbox

gfisch13 — Thu, 19 Oct 2017 14:42:23 GMT

I think its me not being clear enough, here is a screen shot of what the table looks like when I'm done applying the code I show above..............(Flight, doesn't look to be calculating quite right at the moment but I think this picture will help. The CC_Classification field is the one I'm creating...........

Re: Keyword Listbox

effinty2112 — Thu, 19 Oct 2017 15:05:53 GMT

Hi george,

I hope this is helpful:

Lines:

Load

Where Not WildMatch(Values,'*PRCH_HRCH_NM*','*MCC_DSCR*');

Load

Distinct

Subfield(SubField(Lines,' ',1),Chr(39),2) as CC_Classifications,

if(WildMatch(Lines,'*PRCH_HRCH_NM*'),'PRCH_HRCH_NM','MCC_DSCR') as [Search Fields],

Trim(

PurgeChar(

Subfield(

SubField(

Right(Lines, Len(Lines) - len(SubField(Lines,' ',1))),

'(',2),

','),'"'),')'))

as Values;

LOAD Lines

FROM

Lines.txt

(txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);

Giving:

CC_Classifications	Search Fields	Values
Accomodation	PRCH_HRCH_NM	Lodging
Cable/Tele	MCC_DSCR	Book
Cable/Tele	MCC_DSCR	cable
Cable/Tele	MCC_DSCR	Computer
Cable/Tele	MCC_DSCR	Direct market
Cable/Tele	MCC_DSCR	Florist
Cable/Tele	MCC_DSCR	Phone
Cable/Tele	MCC_DSCR	Telecom
CCC	PRCH_HRCH_NM	Invalid MCC
Flight	PRCH_HRCH_NM	Airlines
Flight	MCC_DSCR	ESTA
Flight	MCC_DSCR	travel agen
Flight	MCC_DSCR	Visa
Food/Drink	PRCH_HRCH_NM	Eating
Food/Drink	PRCH_HRCH_NM	Food
Govt	PRCH_HRCH_NM	Govern
Health	MCC_DSCR	dental
Health	PRCH_HRCH_NM	Health service
Health	MCC_DSCR	medical
Miscellaneous	MCC_DSCR	Advertis
Miscellaneous	MCC_DSCR	consulting
Miscellaneous	MCC_DSCR	Contractors
Miscellaneous	MCC_DSCR	data prc
Miscellaneous	MCC_DSCR	elsewhere
Miscellaneous	MCC_DSCR	Engineering
Miscellaneous	MCC_DSCR	Information
Miscellaneous	MCC_DSCR	premiums
Miscellaneous	MCC_DSCR	Preparation
Miscellaneous	MCC_DSCR	Print
Miscellaneous	MCC_DSCR	Residential
Org/Sch/College	MCC_DSCR	Associa
Org/Sch/College	MCC_DSCR	College
Org/Sch/College	MCC_DSCR	Organi
Org/Sch/College	MCC_DSCR	School
Org/Sch/College	MCC_DSCR	Subscrip
Post/Gov	MCC_DSCR	Courier
Post/Gov	MCC_DSCR	Freight
Post/Gov	MCC_DSCR	Postal
Rec	MCC_DSCR	Amusement
Rec	MCC_DSCR	athltic
Rec	MCC_DSCR	Bands
Rec	MCC_DSCR	Bowling
Rec	MCC_DSCR	Caterers
Rec	MCC_DSCR	Dolphin
Rec	MCC_DSCR	Golf
Rec	MCC_DSCR	Recreation
Rec	MCC_DSCR	Theatrical
Rec	MCC_DSCR	Tourist
Shopping	MCC_DSCR	Building
Shopping	PRCH_HRCH_NM	Clothing
Shopping	MCC_DSCR	Drapery
Shopping	MCC_DSCR	Drugs
Shopping	MCC_DSCR	Electrical parts
Shopping	MCC_DSCR	Fuel dealers
Shopping	MCC_DSCR	jigsaw
Shopping	MCC_DSCR	Motion
Shopping	MCC_DSCR	Musical
Shopping	MCC_DSCR	Paint
Shopping	MCC_DSCR	Perfume
Shopping	MCC_DSCR	Pet shop
Shopping	MCC_DSCR	Photof
Shopping	MCC_DSCR	Propane
Shopping	MCC_DSCR	Warehous
Transport	MCC_DSCR	Automo
Transport	PRCH_HRCH_NM	Automobile
Transport	MCC_DSCR	Bus lines
Transport	MCC_DSCR	Cabs
Transport	MCC_DSCR	Taxi
Transport	MCC_DSCR	Transp
Transport	PRCH_HRCH_NM	Transp
Transport	PRCH_HRCH_NM	Vehicle
Transport	MCC_DSCR	Washes
Transport	PRCH_HRCH_NM	washes

Regards

Andrew

Re: Keyword Listbox

sunny_talwar — Tue, 24 Oct 2017 18:06:30 GMT

Check out this

Search Recipes | Qlikview Cookbook

Re: Keyword Listbox

gfisch13 — Tue, 24 Oct 2017 18:26:08 GMT

This is very close to what I need, but the question remains when I have multiple values I want to map to.

So for instance........if I use Shopping in the inline table........there are two fields PRCH_HRCH_NM and MCC_DSCR

where I'm looking for these values……………

'Clothing" Pet shop","Motion","Building","jigsaw","Perfume","Propane","Fuel dealers","Drapery","Musical","Warehous","Drugs","Paint","Electrical parts","Photof"

I want to classify all of those as ‘shopping’ and will include shopping in a list box so I can select it on my dashboard.

I don’t understand how to map the multiple values.

Re: Keyword Listbox

sunny_talwar — Tue, 24 Oct 2017 18:30:37 GMT

Would you be able to show 2-3 lines of what are you mapping from. And I guess this is your mapping table? Are you looking for values in MCC_DSCR and trying to map them to CC_Classifications?

Re: Keyword Listbox

gfisch13 — Tue, 24 Oct 2017 18:35:44 GMT

That is a subset of my data table put together in ACL, in essence the completed table. the CC_Classification is the one word classification I want to include in a list box to group certain records............I'm searching for all those key words I list in PRCH_HRCH_NM and MCC_DSCR.