Focal Point
[SOLVED]append columns to another column without using JOIN or UNION

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8317061496

August 17, 2020, 05:55 AM
nox
[SOLVED]append columns to another column without using JOIN or UNION
Hi,

Let say I have following table:

 
 
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
 

how can I accomplish this result?

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


WebFOCUS v8.2.06 , Windows
August 17, 2020, 08:54 AM
MartinY
Hi nox,

please use to code tag when posting sample code and/or data/layout. It's the last icon on the ribbon that look like the below
</>


I am not sure to 100% understand your request.
bill_01, bill_02 and bill_03 are field name and 100, 113, 120, ... are possible value within each field ?

Then you want to have the each field name to become one unique column and display their respective value beside ?

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



WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
August 17, 2020, 11:34 PM
nox
Hi MartinY,

I try your code and the result like following:

 
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

 


this close to what I want, but not exactly. If using Car as example it should be something like this:

 

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


 



WebFOCUS v8.2.06 , Windows
August 18, 2020, 07:09 AM
MartinY
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



WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
August 18, 2020, 07:45 AM
dbeagan
This seems to work:

 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
  

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


WebFOCUS 8.2.06
August 21, 2020, 02:23 AM
nox
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.


Hi dbeagan,

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


WebFOCUS v8.2.06 , Windows
August 21, 2020, 08:49 AM
MartinY
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.

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

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 ?

What is the purpose of this ? It may help us to understand what you are trying to accomplish


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
August 22, 2020, 11:58 AM
nox
Hi Martin,

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


I remove this line, and the result is no different with it on. wander if something have to do with WebFOCUS version? (the version that I tested was on 8.206)

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 ?


As you can see my first example would be base on a bill table. The original table was several bill on easch column( Bill_1, Bill_2, Bill_3 and so on), and my objective is to get those column value turn into single column, but in order to know each value is belong to which Bill, will need to DEFINE another field to display what this bill was belong to. but some of Bill's value may repeated, so need to list every single field.
hope this help.

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


WebFOCUS v8.2.06 , Windows
August 23, 2020, 07:43 AM
Danny-SRL
Hi Nox,
This should do the trick:
 
-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
 

If you have more than 3 columns of bill change the "to" value in the -REPEAT statement.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

August 23, 2020, 09:16 PM
nox
Hi Danny,

I see what your code doing, and I apologize for my bad example is misleading solution. I should have using this example would be better:

 
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


 

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


WebFOCUS v8.2.06 , Windows
August 23, 2020, 10:06 PM
dbeagan
quote:
I try your code, it is close to what I want as well, but the result is all in one column.

You could use the code to make columns like this:
  
 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



WebFOCUS 8.2.06
August 26, 2020, 11:26 PM
nox
Hi,

Sorry for my late reply, I end up using dbeagan's first example to save 'data' as .csv, then manually create another synonym of that data.csv. After using TOKEN function to split the column into 2 new defined fields. Thank you all for your ideas!


WebFOCUS v8.2.06 , Windows
August 30, 2020, 01:29 PM
Danny-SRL
Hi Nox,
Pardon my being intrusive, but I think that your solution, coding a MASTER manually, defeats the purpose of WebFOCUS's power. See a slight modification of the code I suggested:
-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
  

As you prefer...


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF