Electric_bill | Water_bill | Telephone_bill
100           | 120        | 104
113           | 100        | 123
120           | 170        | 150
 
 

 

BILL_LIST(define)        | BILL (define)

Electric_bill            | 100
Electric_bill            | 113
Electric_bill            | 120
water_bill               | 120
water_bill               | 100
water_bill               | 170
Telephone_bill           | 104
Telephone_bill           | 123
Telephone_bill           | 150
 

</>

-*-* Originally, COUNTRY and CAR are distinct columns, they now become one unique column named FLD1
TABLE FILE CAR
BY CAR/A20 AS 'FLD1'
BY MODEL   AS 'FLD2'
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD AS FILE1
END
-RUN

TABLE FILE CAR
BY COUNTRY/A20 AS 'FLD1'
BY MODEL       AS 'FLD2'
WHERE COUNTRY EQ 'ITALY'
ON TABLE HOLD AS FILE2
END
-RUN

TABLE FILE FILE1
BY FLD1
BY FLD2
ON TABLE HOLD AS FINALFILE
MORE
FILE FILE2
END
-RUN

TABLE FILE FINALFILE
BY FLD1
BY FLD2
ON TABLE SET BYDISPLAY ON
END
-RUN

 
FLD1 	FLD2
ITALY 	2000 4 DOOR BERLINA
ITALY 	2000 GT VELOCE
ITALY 	2000 SPIDER VELOCE
ITALY 	DORA 2 DOOR
JAGUAR 	V12XKE AUTO
JAGUAR 	XJ12L AUTO
JENSEN 	INTERCEPTOR III
TRIUMPH 	TR7

 

 

FLD1 	FLD2
COUNTRY ITALY
COUNTRY ITALY
COUNTRY	ITALY
COUNTRY ITALY
MODEL   2000 4 DOOR BERLINA
MODEL 	2000 GT VELOCE
MODEL 	2000 SPIDER VELOCE
MODEL 	DORA 2 DOOR


 

TABLE FILE CAR
BY TOTAL COMPUTE FLD1 /A10 = 'COUNTRY';
BY COUNTRY/A20 AS 'FLD2'
ON TABLE HOLD AS FILE1
END
-RUN

TABLE FILE CAR
BY TOTAL COMPUTE FLD1 /A10 = 'MODEL';
BY MODEL/A20   AS 'FLD2'
ON TABLE HOLD AS FILE2
END
-RUN

TABLE FILE FILE1
BY FLD1
BY FLD2
ON TABLE HOLD AS FINALFILE
MORE
FILE FILE2
END
-RUN

TABLE FILE FINALFILE
BY FLD1
BY FLD2
ON TABLE SET BYDISPLAY ON
END
-RUN

 SET PAGE=NOLEAD
 FILEDEF data DISK baseapp/data.txt

 TABLE FILE CAR
 PRINT COUNTRY IN 0
  OVER CAR     IN 0
  OVER MODEL   IN 0
 HEADING
 " "
 WHEN 1=0
 ON TABLE HOLD AS data FORMAT WP
 END
  

 

FLD1    FLD2
COUNTRY ENGLAND
COUNTRY FRANCE
COUNTRY ITALY
COUNTRY JAPAN
COUNTRY W GERMANY
MODEL   100 LS 2 DOOR AUTO
MODEL   2000 4 DOOR BERLINA
MODEL   2000 GT VELOCE
MODEL   2000 SPIDER VELOCE
MODEL   2002 2 DOOR
MODEL   2002 2 DOOR AUTO
MODEL   3.0 SI 4 DOOR
MODEL   3.0 SI 4 DOOR AUTO
MODEL   504 4 DOOR
MODEL   530I 4 DOOR
MODEL   530I 4 DOOR AUTO
MODEL   B210 2 DOOR AUTO
MODEL   COROLLA 4 DOOR DIX A
MODEL   DORA 2 DOOR
MODEL   INTERCEPTOR III
MODEL   TR7
MODEL   V12XKE AUTO
MODEL   XJ12L AUTO



 

 ON TABLE SET BYDISPLAY ON  

quote:

Hi MartinY,

I test your code, but endup something like this:

FLD1 FLD2
COUNTRY ENGLAND
COUNTRY FRANCE
COUNTRY ITALY
COUNTRY JAPAN
COUNTRY W GERMANY
MODEL 100 LS 2 DOOR AUTO
MODEL 2000 4 DOOR BERLINA
MODEL 2000 GT VELOCE
MODEL 2000 SPIDER VELOCE
MODEL 2002 2 DOOR
MODEL 2002 2 DOOR AUTO
MODEL 3.0 SI 4 DOOR
MODEL 3.0 SI 4 DOOR AUTO
MODEL 504 4 DOOR
MODEL 530I 4 DOOR
MODEL 530I 4 DOOR AUTO
MODEL B210 2 DOOR AUTO
MODEL COROLLA 4 DOOR DIX A
MODEL DORA 2 DOOR
MODEL INTERCEPTOR III
MODEL TR7
MODEL V12XKE AUTO
MODEL XJ12L AUTO

As notice COUNTRY is not repeat. I want it can be repeat, which I see
ON TABLE SET BYDISPLAY ON
but seem BYDISPLAY ON is not working.

quote:

SET BYDISPLAY ON is to "avoid" the masking (non-display) of a repeated BY value. So, yes it does work.
Remove it and you will see the difference

quote:

What you want is to have as many time a model exist for a country, the same number of time the country displayed ?
In other words, if there is 4 models for Italy, you want Italy displayed 4 times ?

 
-REPEAT #NOX FOR &I FROM 1 TO 3;
-SET &HTYPE=IF &I EQ 1 THEN 'HOLD FORMAT ALPHA' ELSE SAVE;
DEFINE FILE nox
BILL_LIST/A8='BILL_0&I';
BILL/I3=BILL_0&I;
END
-* 
END
TABLE FILE NOX
BY BILL_LIST
BY BILL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE &HTYPE
END
-RUN
FILEDEF HOLD DISK HOLD.FTM (APPEND
-#NOX
-* 
TABLE FILE HOLD
PRINT BILL
BY BILL_LIST
END
 

 
Let say I have following table: (may have more than these 3 bill's columns)

 
Electric_bill | Water_bill | Telephone_bill
100           | 120        | 104
113           | 100        | 123
120           | 170        | 150

 


and I want turn into like this:

 
BILL_LIST(define)        | BILL (define)

Electric_bill            | 100
Electric_bill            | 113
Electric_bill            | 120
water_bill               | 120
water_bill               | 100
water_bill               | 170
Telephone_bill           | 104
Telephone_bill           | 123
Telephone_bill           | 150


 

quote:

I try your code, it is close to what I want as well, but the result is all in one column.

  
 SET PAGE=NOLEAD
 FILEDEF data2  DISK data2.txt
 FILEDEF master DISK data2.mas
 
 TABLE FILE CAR
 PRINT COUNTRY IN 0
  OVER CAR     IN 0
  OVER LENGTH   IN 0
 HEADING
 " "
 WHEN 1=0
 ON TABLE HOLD AS data2 FORMAT WP
 END
-RUN
 
-WRITE master FILENAME=DATA2, SUFFIX=FIX, IOTYPE=STREAM, DATASET=data2.txt,$
-WRITE master   SEGMENT=DATA2, SEGTYPE=S0, $
-WRITE master     FIELDNAME=Blank, ALIAS=E01, USAGE=A01, ACTUAL=A01, $
-WRITE master     FIELDNAME=Field, ALIAS=E02, USAGE=A07, ACTUAL=A07, $
-WRITE master     FIELDNAME=Value, ALIAS=E03, USAGE=A26, ACTUAL=A26, $
 
 TABLE FILE data2
 PRINT Field Value
 END

-REPEAT #NOX FOR &I FROM 1 TO 3;
-SET &HTYPE=IF &I EQ 1 THEN 'HOLD FORMAT ALPHA' ELSE SAVE;
-SET &BILL_TYPE=DECODE &I(1 Electric_bill 2  Water_bill 3 Telephone_bill);
-* more values if necessary
DEFINE FILE nox
BILL_LIST/A20='&BILL_TYPE';
BILL/I3=&BILL_TYPE;
END
-* 
END
TABLE FILE NOX
BY BILL_LIST
BY BILL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE &HTYPE
END
-RUN
FILEDEF HOLD DISK HOLD.FTM (APPEND
-#NOX
-* 
TABLE FILE HOLD
PRINT BILL
BY BILL_LIST
END