Index Creation

Chris Macadam
Rocketeer
Forum|Forum|2 years ago
December 30, 2022

Hy all

i have field for cars license plate ... but users input data all the differents way
like for exemple:

QAA.6814

QAA-6814

QAA 6814

QAA/6814

QAA6814

how to create an index so i can search the data... but ignoring the .-/ (space) ???

------------------------------
Alberto Leal
System Analyst
Millano Distribuidora de Auto Pecas Ltda
Varzea Grande MT BR
------------------------------

Hi Alberto,
One option would be to create an index that calls a BASIC program. If we assume that the license data is stored in attribute 5 of the CARS file, the statement to create the index would look something like this:
   create-index cars a5(call ba,bp, cleanlicense)
Using a full path is preferred so 'ba' is the account and 'bp' is the file where the 'cleanlicense' program is stored.

The program itself is very simple:
   01 subroutine cleanlicense(license)
   02
   03   convert '.- /' to '' in license
   04 return

The final piece is to create an attribute defining item (license) that will make use of the index for selecting:
license
001 a
002 5
003
004
005
006
007
008 a5(call ba,bp, cleanlicense)
009 l
010 10
It is important that the correlative specified in the attribute defining item is exactly the same as the correlative used in the creation of the index otherwise the index will not be used in the selection process.

select cars if license = "qaa6814"
will return any items with the 5 patterns that you used in your example above.

Hope that helps.

------------------------------
Chris Macadam
Technical Support Engineer
Rocket Software
------------------------------

Like

A

+1

Alberto Leal
Author
Inspiring
Forum|Forum|2 years ago
December 30, 2022

Hi Alberto,
One option would be to create an index that calls a BASIC program. If we assume that the license data is stored in attribute 5 of the CARS file, the statement to create the index would look something like this:
   create-index cars a5(call ba,bp, cleanlicense)
Using a full path is preferred so 'ba' is the account and 'bp' is the file where the 'cleanlicense' program is stored.

The program itself is very simple:
   01 subroutine cleanlicense(license)
   02
   03   convert '.- /' to '' in license
   04 return

The final piece is to create an attribute defining item (license) that will make use of the index for selecting:
license
001 a
002 5
003
004
005
006
007
008 a5(call ba,bp, cleanlicense)
009 l
010 10
It is important that the correlative specified in the attribute defining item is exactly the same as the correlative used in the creation of the index otherwise the index will not be used in the selection process.

select cars if license = "qaa6814"
will return any items with the 5 patterns that you used in your example above.

Hope that helps.

------------------------------
Chris Macadam
Technical Support Engineer
Rocket Software
------------------------------

Thanks Chris !!

and i can use like this too?

ARQ.ROOT = "SGVAR050"
ATR.ROOT = '64(call ba,bp, cleanlicense)'
OP.ROOT = "N"
DADO.ROOT = qaa6814

ROOT ARQ.ROOT,"A":ATR.ROOT TO IND.ROOT
....
SEARCH.license :
KEY(OP.ROOT,IND.ROOT,DADO.ROOT,CHAVE50) ELSE GO SEARCH.license
....

------------------------------
Alberto Leal
System Analyst
Millano Distribuidora de Auto Pecas Ltda
Varzea Grande MT BR
------------------------------

Like

Chris Macadam
Rocketeer
Forum|Forum|2 years ago
December 30, 2022

Thanks Chris !!

and i can use like this too?

ARQ.ROOT = "SGVAR050"
ATR.ROOT = '64(call ba,bp, cleanlicense)'
OP.ROOT = "N"
DADO.ROOT = qaa6814

ROOT ARQ.ROOT,"A":ATR.ROOT TO IND.ROOT
....
SEARCH.license :
KEY(OP.ROOT,IND.ROOT,DADO.ROOT,CHAVE50) ELSE GO SEARCH.license
....

------------------------------
Alberto Leal
System Analyst
Millano Distribuidora de Auto Pecas Ltda
Varzea Grande MT BR
------------------------------

Yes, just make sure to change the 'ba' and 'bp' to the correct values for the account and file where 'cleanlicense' is stored.

------------------------------
Chris Macadam
Technical Support Engineer
Rocket Software
------------------------------

Like

+2

Manu Fernandes
Inspiring
Forum|Forum|2 years ago
January 2, 2023

Thanks Chris !!

and i can use like this too?

ARQ.ROOT = "SGVAR050"
ATR.ROOT = '64(call ba,bp, cleanlicense)'
OP.ROOT = "N"
DADO.ROOT = qaa6814

ROOT ARQ.ROOT,"A":ATR.ROOT TO IND.ROOT
....
SEARCH.license :
KEY(OP.ROOT,IND.ROOT,DADO.ROOT,CHAVE50) ELSE GO SEARCH.license
....

------------------------------
Alberto Leal
System Analyst
Millano Distribuidora de Auto Pecas Ltda
Varzea Grande MT BR
------------------------------

hello,

I'll suggest to use the MCNA or MCAN on the correlative of the field.
001 a
002 5
...
008 mcna
009 l
010 10

------------------------------
manu fernandes
------------------------------

Like

A

+1

Alberto Leal
Author
Inspiring
Forum|Forum|2 years ago
January 2, 2023

hello,

I'll suggest to use the MCNA or MCAN on the correlative of the field.
001 a
002 5
...
008 mcna
009 l
010 10

------------------------------
manu fernandes
------------------------------

Hy ...

what means those options ?

MCNA ?

------------------------------
Alberto Leal
System Analyst
Millano Distribuidora de Auto Pecas Ltda
Varzea Grande MT BR
------------------------------

Like

Frank Diaz
Participating Frequently
Forum|Forum|2 years ago
January 2, 2023

Hy all

i have field for cars license plate ... but users input data all the differents way
like for exemple:

QAA.6814

QAA-6814

QAA 6814

QAA/6814

QAA6814

how to create an index so i can search the data... but ignoring the .-/ (space) ???

------------------------------
Alberto Leal
System Analyst
Millano Distribuidora de Auto Pecas Ltda
Varzea Grande MT BR
------------------------------

Will it not be easier to clean it up at point of entry using LICENSE=OCONV(LICENSE,'MCAN') which eliminate all non alpha or numeric characters.

------------------------------
Frank Diaz
Consultant
Compucot Inc
Doral FL US
------------------------------

Like

Frank Diaz
Participating Frequently
Forum|Forum|2 years ago
January 3, 2023

Hy ...

what means those options ?

MCNA ?

------------------------------
Alberto Leal
System Analyst
Millano Distribuidora de Auto Pecas Ltda
Varzea Grande MT BR
------------------------------

MCAN in the OCONV() function only leaves alpha and numeric character, eliminationg all others.

Example:
TEXT='QAA-,;:1234
CRT OCONV(TEXT,'MCAN') would display QAA1234

------------------------------
Frank Diaz
Consultant
Compucot Inc
Doral FL US
------------------------------

Like

+2

Manu Fernandes
Inspiring
Forum|Forum|2 years ago
January 3, 2023

Hy ...

what means those options ?

MCNA ?

------------------------------
Alberto Leal
System Analyst
Millano Distribuidora de Auto Pecas Ltda
Varzea Grande MT BR
------------------------------

hi

It makes reference to 'mask character alpha numeric' used in iconv/oconv() or as convertion/correlation of item-definition.
processing code- mc/ code

If you want to index and have a iconv when you execute select, define like this :
01 A
02 1
03 PLATE
04
05
06
07 MCNA
08 A1(MCNA)
09 L
10 30

007 MCNA perform the iconv/oconv
008 prepare the data before evaluation ; and is used to update index.

( A1 is the field from the file not converted)

FEFVTEST.. A1.......................... PLATE       
                                                    
1          EFV.908                      EFV908      
2          GT!GH89.-45                  GTGH8945    
3          TR'_U!345                    TRU345      
4          FG.90/522                    FG90522     
5          TRW3-58*P                    TRW358P

SAMPLE where I select with -.! ... non-alphanumeric chars which are not the one into the file.

LIST FILE WITH PLATE = "EFV-9.!08" A1 PLATE       
                                                                    
FILE...... A1.......................... PLATE                        
                                                                    
1          EFV.908                      EFV908

I hope this help
manu

------------------------------
manu fernandes
------------------------------

Like

A

+1

Alberto Leal
Author
Inspiring
Forum|Forum|2 years ago
January 3, 2023

hi

It makes reference to 'mask character alpha numeric' used in iconv/oconv() or as convertion/correlation of item-definition.
processing code- mc/ code

If you want to index and have a iconv when you execute select, define like this :
01 A
02 1
03 PLATE
04
05
06
07 MCNA
08 A1(MCNA)
09 L
10 30

007 MCNA perform the iconv/oconv
008 prepare the data before evaluation ; and is used to update index.

( A1 is the field from the file not converted)

FEFVTEST.. A1.......................... PLATE       
                                                    
1          EFV.908                      EFV908      
2          GT!GH89.-45                  GTGH8945    
3          TR'_U!345                    TRU345      
4          FG.90/522                    FG90522     
5          TRW3-58*P                    TRW358P

SAMPLE where I select with -.! ... non-alphanumeric chars which are not the one into the file.

LIST FILE WITH PLATE = "EFV-9.!08" A1 PLATE       
                                                                    
FILE...... A1.......................... PLATE                        
                                                                    
1          EFV.908                      EFV908

I hope this help
manu

------------------------------
manu fernandes
------------------------------

Thanks Manu...

that worked perfectly !!

using the MCNA ... quick and easy

------------------------------
Alberto Leal
System Analyst
Millano Distribuidora de Auto Pecas Ltda
Varzea Grande MT BR
------------------------------

Like

+1

John Jenkins
Participating Frequently
Forum|Forum|2 years ago
January 5, 2023

Hy all

i have field for cars license plate ... but users input data all the differents way
like for exemple:

QAA.6814

QAA-6814

QAA 6814

QAA/6814

QAA6814

how to create an index so i can search the data... but ignoring the .-/ (space) ???

------------------------------
Alberto Leal
System Analyst
Millano Distribuidora de Auto Pecas Ltda
Varzea Grande MT BR
------------------------------

If you are considering world-wide number plates rather than just a specific country of which you have specific knowledge I recommend Neil Parker's guides on number plates. Some national plates give significance to punctuation symbols https://www.alpca.org/halloffame/neilparker/ and knew him and his wife Diane very well as we worked on multiple MV projects together. Sadly missed...

JJ
,

------------------------------
John Jenkins
Thame, Oxfordshire
------------------------------

JJ

Like

10 replies

Recent badge winners

Recent badge winners

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register:

Scanning file for viruses.

This file cannot be downloaded