Help needed: expanding hyphenated ranges

July 03, 2008, 09:34 AM

mitch goudy

I need help.

I have data that includes hyphenated ranges of reference designators (e.g. R3-12) for each part number that I need to expand (R3, R4, R5, R6, etc). Not only that, but I also need to create new records for each references designators.

For Example:
INPUT:
P/N RD
584894 E1-E9
584894 C5-C8
584900 CR9-CR-16
584900 CR1-CR8
584937 E1-E4

OUTPUT:
P/N RD
584894 E1
584894 E2
584894 E3
584894 E4
584894 E5
Etc

I have been able to use GETTOK and SUBSTR to get the data to this point:

P/N RD RD1 RD2 RANGE RDCHR
584894 E1-E9 1 9 9 E
584894 C5-C8 5 8 4 C
584900 CR9-CR-16 9 16 8 CR
584900 CR1-CR8 1 8 8 CR
584937 E1-E4 1 4 4 E

But I can’t get any further to my goal.

Thanks in Advance

July 03, 2008, 11:00 AM

Francis Mariani

It took a bit of experimentation, but I think I've nailed it, using a variation of the "McGyver Technique". I've created a file called NUMBERS that contains all the possible numeric values in the "reference designators".

-*-- Create the master for Numbers -------------------------
APP FI DATAMAST DISK NUMBERS.MAS
-RUN

-WRITE DATAMAST FILE=NUMBERS, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=NUMBER, ALIAS=E02, USAGE=I02, ACTUAL=A02, $

-*-- Create the data file for Numbers ----------------------
FILEDEF NUMBERS DISK NUMBERS.FTM
-RUN

-WRITE NUMBERS 01
-WRITE NUMBERS 02
-WRITE NUMBERS 03
-WRITE NUMBERS 04
-WRITE NUMBERS 05
-WRITE NUMBERS 06
-WRITE NUMBERS 07
-WRITE NUMBERS 08
-WRITE NUMBERS 09
-WRITE NUMBERS 10
-WRITE NUMBERS 11
-WRITE NUMBERS 12
-WRITE NUMBERS 13
-WRITE NUMBERS 14
-WRITE NUMBERS 15
-WRITE NUMBERS 16
-WRITE NUMBERS 17
-WRITE NUMBERS 18
-WRITE NUMBERS 19
-WRITE NUMBERS 20

-*-- Create the master for Parts ---------------------------
APP FI DATAMAST DISK PARTS.MAS
-RUN

-WRITE DATAMAST FILE=PARTS, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=PART_NO, ALIAS=E01, USAGE=A06, ACTUAL=A06, $
-WRITE DATAMAST   FIELD=FILL1  , ALIAS=E02, USAGE=A01, ACTUAL=A01, $
-WRITE DATAMAST   FIELD=REF_DES, ALIAS=E03, USAGE=A10, ACTUAL=A10, $

-*-- Create the data file for Parts ------------------------
FILEDEF PARTS DISK PARTS.FTM
-RUN

-WRITE PARTS 584894 E1-E9
-WRITE PARTS 584894 C5-C8
-WRITE PARTS 584900 CR9-CR16
-WRITE PARTS 584900 CR1-CR8
-WRITE PARTS 584937 E1-E4

-* GETTOK(infield, inlen, toknum, delim, outlen, outfield)
-* SUBSTR(inlength, parent, start, end, sublength, outfield)

DEFINE FILE PARTS
REF_DES1/A04 = GETTOK(REF_DES, 10, 1, '-', 4, 'A04');
REF_DES1A/A02 = SUBSTR(4, REF_DES1, 1, 1, 1, 'A02');
REF_DES1B/A02 = SUBSTR(4, REF_DES1, 1, 2, 2, 'A02');
REF_DES1C/A02 = SUBSTR(4, REF_DES1, 2, 3, 2, 'A02');
REF_DES1D/A02 = SUBSTR(4, REF_DES1, 3, 4, 2, 'A02');
REF_DES_CODE/A02 = IF REF_DES1C GE '01' AND REF_DES1C LE '99' THEN REF_DES1A ELSE REF_DES1B;
REF_DES_NUM1/I02 = IF REF_DES1C GE '01' AND REF_DES1C LE '99' THEN EDIT(REF_DES1C) ELSE EDIT(REF_DES1D);

REF_DES2/A04 = GETTOK(REF_DES, 10, 2, '-', 4, 'A04');
REF_DES2A/A02 = SUBSTR(4, REF_DES2, 1, 1, 1, 'A02');
REF_DES2B/A02 = SUBSTR(4, REF_DES2, 1, 2, 2, 'A02');
REF_DES2C/A02 = SUBSTR(4, REF_DES2, 2, 3, 2, 'A02');
REF_DES2D/A02 = SUBSTR(4, REF_DES2, 3, 4, 2, 'A02');
REF_DES_NUM2/I02 = IF REF_DES2C GE '01' AND REF_DES2C LE '99' THEN EDIT(REF_DES2C) ELSE EDIT(REF_DES2D);

DUMMY/A01 = 'X';
END
-RUN

TABLE FILE PARTS
PRINT
PART_NO
REF_DES
REF_DES1
REF_DES2
REF_DES_CODE
REF_DES_NUM1
REF_DES_NUM2
BY DUMMY
ON TABLE HOLD AS H001 FORMAT FOCUS INDEX DUMMY
END
-RUN

DEFINE FILE NUMBERS
DUMMY/A01 = 'X';
END
-RUN

TABLE FILE NUMBERS
PRINT *
BY DUMMY
ON TABLE HOLD AS H002 FORMAT FOCUS INDEX DUMMY
END
-RUN

JOIN CLEAR *
JOIN DUMMY IN H001 TO ALL DUMMY IN H002 AS J1

DEFINE FILE H001
NUMBERX/A02 = FTOA(NUMBER, '(F2)', 'A02');
NEW_REF_DES/A04 = REF_DES_CODE || LJUST(2, NUMBERX, 'A02');
END
-RUN

TABLE FILE H001
PRINT
PART_NO
NEW_REF_DES

WHERE NUMBER FROM REF_DES_NUM1 TO REF_DES_NUM2
END
-RUN

The only case I did not take into account is the "CR9-CR-16", I've assumed it should be "CR9-CR16".

July 03, 2008, 01:59 PM

mitch goudy

Thanks Francis, I give it a try!

Yes, that was a typo, s/b CR9-CR16.

July 04, 2008, 06:26 AM

Danny-SRL

Mitch,

I am a firm believer in McGuyver! So here is my McGuyver solution:

  
-* File mitch1.fex
JOIN BLANK WITH RD IN MITCH TO BLANK IN FSEQ AS M
DEFINE FILE MITCH
BLANK/A1 WITH RD = ' ';
-REPEAT #RD FOR &I FROM 1 TO 2;
RD&I/A4=GETTOK(RD, 9, &I, '-', 4, 'A4');
LRD&I/I1=ARGLEN(4, RD&I, 'I1');
RDA&I/A2=IF LRD&I EQ 2 THEN EDIT(RD&I, '9') ELSE 
 IF LRD&I EQ 4 THEN EDIT(RD&I, '99') ELSE 
 IF EDIT(RD&I, '99') CONTAINS '0' OR '1' OR '2' OR '3' OR '4' OR '5' OR '6' OR '7' OR '8' OR '9' THEN EDIT(RD&I, '9') ELSE 
    EDIT(RD&I, '99'); 
RDB&I/A2=IF LRD&I EQ 2 THEN EDIT(RD&I, '$9') ELSE 
 IF LRD&I EQ 4 THEN EDIT(RD&I, '$$99') ELSE 
 IF EDIT(RD&I, '99') CONTAINS '0' OR '1' OR '2' OR '3' OR '4' OR '5' OR '6' OR '7' OR '8' OR '9' THEN EDIT(RD&I, '$99') ELSE 
    EDIT(RD&I, '$$9');
RDN&I/I2=EDIT(RDB&I);	 
-#RD
RDN/I2=RDN1 - 1 + COUNTER;
END
TABLE FILE MITCH
PRINT RDN
BY PN BY RDA1
WHERE RDN LE RDN2
ON TABLE SET BYDISPLAY ON
END

Mitch file:

  
FILE=MITCH, SUFFIX=FIX,
 DATASET=C:\IBI\APPS\FOCALPOINT\MITCH.TXT, $
 SEGNAME=SEG1, SEGTYPE=S0
 FIELD=PN, ALIAS=PN, USAGE=I6L, ACTUAL=A6, $
 FIELD=f1, ALIAS=f1, USAGE=A1, ACTUAL=A1, $
 FIELD=RD, ALIAS=RD, USAGE=A9, ACTUAL=A9, $

584894 E1-E9
584894 C5-C8
584900 CR9-CR16
584900 CR1-CR8
584937 E1-E4

McGuyver:

  
 FILENAME=FSEQ,SUFFIX=FIX,
    DATASET=C:\IBI\APPS\FOCALPOINT\FSEQ.DAT
  SEGNAME=CHAR1,SEGTYPE=S0
   FIELDNAME=BLANK,BLANK,A1,A1,$
  SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
   FIELDNAME=CHAR,CHARS,A1,A1,$
   FIELDNAME=COUNTER,ORDER,I2,I4,$

 FILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVER