On a HOLD file I have created a table called MILES. It has 2 columns Date and Name.
DATE MILES
3/29/2010 MILES 1
3/31/2010 MILES 2
4/5/2010 MILES 4
4/14/2010 MILES 3
4/16/2010 MILES 5
I need to add a new column called “LABEL” which will represent each MILE with a Name…For example.
LABEL DATE MILES
A 2010/03/29 MILES 1
B 2010/03/31 MILES 2
C 2010/04/05 MILES 4
D 2010/04/14 MILES 3
E 2010/04/16 MILES 5
F 2010/04/22 MILES 6

NOTE: The DATE and MILES is dynamically generated.
Please could anyone suggest how to achieve this. Im still new to WB so would need a more elaborate explanation.
Thanks for all the help in advance..

WB Version: 7.6.4
Platform: Windows

This message has been edited. Last edited by: Kerry,

The use of DEFINE or COMPUTE may be helpful here. Look for those tools. Is there any logic required for the assignment of LABEL?

jimster06,

The first MILE should have the Label 'A', the 2nd should be 'B' and so on.

I was thinking of implementing it as follows. I dont know if this is a good way to approach it, but this is all I can think of now.
1. do a COUNT * on the MILE table and store it in a variable &COUNTER.
2. Use a FOR loop for 1 to &COUNTER
3. -TYPE 'A' in row1 of LABEL,
-TYPE 'B' in row2 of LABEL.....and so on..

I started with :

TABLE FILE MILE
COUNT * AS COUNTER
ON TABLE HOLD AS CNT_FILE FORMAT FOCUS
END
-RUN
-READ MILE &COUNTER.A21

-TYPE &COUNTER
END

I get an error message as follows:
" 0 NUMBER OF RECORDS IN TABLE= 40 LINES= 40
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
0 ERROR AT OR NEAR LINE 18 IN PROCEDURE test_spendate1.fex
(FOC339) DIALOGUE MANAGER -READ FAILED: CHECK FILEDEF OR ALLOCATION FOR: -READ
TMPMILESTONE &COUNTER.A21"

Apparently you have 40 records in the MILE table. What should the LABEL field be after record 26?

I will call it AA, BB, CC and so on..

Try to work out what's going on in this example:

SET HOLDFORMAT=ALPHA
SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
-RUN

FILEDEF MASTER DISK labelf.mas
-RUN

-WRITE MASTER FILENAME=LABELF, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=LABELF, $
-WRITE MASTER FIELDNAME=LABELN, FORMAT=I4, ACTUAL=A4, $
-WRITE MASTER FIELDNAME=FILLER, FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=LABELA, FORMAT=A2, ACTUAL=A2, $

FILEDEF LABELF DISK LABELF.txt
-RUN

-WRITE LABELF 0001 A
-WRITE LABELF 0002 B
-WRITE LABELF 0003 C
-WRITE LABELF 0004 D
-WRITE LABELF 0005 E
-WRITE LABELF 0006 F
-WRITE LABELF 0007 G
-WRITE LABELF 0008 H
-WRITE LABELF 0009 I
-WRITE LABELF 0010 J
-WRITE LABELF 0011 K
-WRITE LABELF 0012 L
-WRITE LABELF 0013 M
-WRITE LABELF 0014 N
-WRITE LABELF 0015 O
-WRITE LABELF 0016 P
-WRITE LABELF 0017 Q
-WRITE LABELF 0018 R
-WRITE LABELF 0019 S
-WRITE LABELF 0020 T
-WRITE LABELF 0021 U
-WRITE LABELF 0022 V
-WRITE LABELF 0023 W
-WRITE LABELF 0024 X
-WRITE LABELF 0025 Y
-WRITE LABELF 0026 Z
-WRITE LABELF 0027 AA
-WRITE LABELF 0028 BB
-WRITE LABELF 0029 CC
-WRITE LABELF 0030 DD
-WRITE LABELF 0031 EE
-WRITE LABELF 0032 FF
-WRITE LABELF 0033 GG
-WRITE LABELF 0034 HH
-WRITE LABELF 0035 II
-WRITE LABELF 0036 JJ
-WRITE LABELF 0037 KK
-WRITE LABELF 0038 LL
-WRITE LABELF 0039 MM
-WRITE LABELF 0040 NN
-WRITE LABELF 0041 OO
-WRITE LABELF 0042 PP
-WRITE LABELF 0043 QQ
-WRITE LABELF 0044 RR
-WRITE LABELF 0045 SS
-WRITE LABELF 0046 TT
-WRITE LABELF 0047 UU
-WRITE LABELF 0048 VV
-WRITE LABELF 0049 WW
-WRITE LABELF 0050 XX
-WRITE LABELF 0051 YY
-WRITE LABELF 0052 ZZ
-WRITE LABELF 0053 AA
-WRITE LABELF 0054 BB
-WRITE LABELF 0055 CC
-WRITE LABELF 0056 DD
-WRITE LABELF 0057 EE
-WRITE LABELF 0058 FF
-WRITE LABELF 0059 GG
-WRITE LABELF 0060 HH
-WRITE LABELF 0061 II
-WRITE LABELF 0062 JJ
-WRITE LABELF 0063 KK
-WRITE LABELF 0064 LL
-WRITE LABELF 0065 MM
-WRITE LABELF 0066 NN
-WRITE LABELF 0067 OO
-WRITE LABELF 0068 PP
-WRITE LABELF 0069 QQ
-WRITE LABELF 0070 RR
-WRITE LABELF 0071 SS
-WRITE LABELF 0072 TT
-WRITE LABELF 0073 UU
-WRITE LABELF 0074 VV
-WRITE LABELF 0075 WW
-WRITE LABELF 0076 XX
-WRITE LABELF 0077 YY
-WRITE LABELF 0078 ZZ

TABLE FILE CENTURYSALES
SUM
QUANTITY
COMPUTE LABELI/I4 = LABELI + 1; NOPRINT
COMPUTE LABELN/A4 = EDIT(LABELI);
BY SALESREP

WHERE RECORDLIMIT EQ 500

ON TABLE HOLD AS H001
END
-RUN

JOIN LABELN IN H001 TO LABELN IN LABELF AS J1
-RUN

TABLE FILE H001
PRINT
QUANTITY
SALESREP
LABELN
LABELA
END

Can you use a number instead of a letter as a label? If so, the LIST command is an easy solution.

TABLE FILE MILES
 LIST DATE NAME
END

Francis Mariani,

Thank you so much for the help. I had to make very minor changes to fit my code and it worked perfectly fine. The example was really helpful.

My final table is done and im so excited...

[SOLVED]

You can flag this thread as solved by editing the original post and changing the title.

Regards,

You could also use a combination of HEXBYT and IMOD on a sequence number -

DEFINE FILE GGSALES
  REM/I2  = IMOD(SEQ_NO - 1, 26, 'I2');
  MULT/I3 = (SEQ_NO - 1) / 26;
  CHAR/A1 = HEXBYT(REM + 65,'A1');
  KEY/A6  = IF MULT EQ 0 THEN CHAR
       ELSE IF MULT EQ 1 THEN CHAR || CHAR
       ELSE IF MULT EQ 2 THEN CHAR || CHAR || CHAR
       ELSE IF MULT EQ 3 THEN CHAR || CHAR || CHAR || CHAR
       ELSE IF MULT EQ 4 THEN CHAR || CHAR || CHAR || CHAR || CHAR
       ELSE IF MULT EQ 5 THEN CHAR || CHAR || CHAR || CHAR || CHAR || CHAR ELSE '????';
END
TABLE FILE GGSALES
PRINT UNITS
   BY SEQ_NO NOPRINT
   BY KEY
   BY CATEGORY
WHERE SEQ_NO LE 80
END

T