Keyword Listbox

[gfisch13]

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'

[pradosh_thakur]

did you try the function WILDMATCH() in QV?

regards

Pradosh

[gfisch13]

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

[fvelascog72]

Hi,

Try with attached file.

Saludos.

[felipedl]

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;

[effinty2112]

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

[gfisch13]

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?

[effinty2112]

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

[gfisch13]

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...........

[effinty2112]

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(

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