Focal Point
[SOLVED] Building a new column with prior values

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

October 24, 2019, 03:58 PM
Tomsweb
[SOLVED] Building a new column with prior values
I am trying to create a prototype from the CAR file.
My goal is to create a new column, SALES2.

I want to carry the last value for the BMW cars (15600)
to the 1st record of the SALES2 column (the red text).


<HTML>
<HEAD>
<TITLE> BUILD COLUMNS WITH NUMBERS
</TITLE>
</HEAD>

<BODY>

<TABLE ALIGN=CENTER WIDTH=300 BORDER="2">
<TR>
<TD ALIGN=CENTER>COUNTRY</TD><TD ALIGN=CENTER>CAR</TD><TD ALIGN=CENTER>SALES<TD ALIGN=CENTER>SALES2</TD>
</TR>
<TR>
<TD ALIGN=CENTER>W GERMANY</TD><TD ALIGN=CENTER>AUDI<TD ALIGN=CENTER>7800</TD><TD ALIGN=CENTER><FONT COLOR="RED">[b]15600[/b]</FONT></TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>8950</TD><TD ALIGN=CENTER> </TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>8900</TD><TD ALIGN=CENTER> </TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>14000</TD><TD ALIGN=CENTER> </TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>18940</TD><TD ALIGN=CENTER> </TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>14000</TD><TD ALIGN=CENTER> </TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>15600</TD><TD ALIGN=CENTER> </TD>
</TR>


<!--- Identifying a Report Component in a WebFOCUS Style Sheet ---?
</TABLE>

</BODY>

</HTML>


Has anyone ever done this in their reports?

Thank you.

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


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
October 24, 2019, 04:28 PM
Tomsweb
I thought of this on the elevator. 

TABLE FILE CAR
SUM 
MAX.SALES
BY COUNTRY
-*
PRINT SALES
BY COUNTRY
WHERE COUNTRY EQ 'W GERMANY';
END
-RUN
-EXIT



Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
October 24, 2019, 05:02 PM
Tomsweb
quote:
Originally posted by Tomsweb:
I thought of this on the elevator. 

TABLE FILE CAR
SUM 
LST.SALES
BY COUNTRY
-*
PRINT SALES
BY COUNTRY
WHERE COUNTRY EQ 'W GERMANY';
END
-RUN
-EXIT



Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
October 24, 2019, 06:22 PM
Tomsweb
quote:
Originally posted by Tomsweb:
I am trying to create a prototype from the CAR file.
My goal is to create a new column, SALES2.

I want to carry the last value for the BMW cars (15600)
to the 1st record of the SALES2 column (the red text).


<HTML>
<HEAD>
<TITLE> BUILD COLUMNS WITH NUMBERS
</TITLE>
</HEAD>

<BODY>

<TABLE ALIGN=CENTER WIDTH=300 BORDER="2">
<TR>
<TD ALIGN=CENTER>COUNTRY</TD><TD ALIGN=CENTER>CAR</TD><TD ALIGN=CENTER>SALES<TD ALIGN=CENTER>SALES2</TD>
</TR>
<TR>
<TD ALIGN=CENTER>W GERMANY</TD><TD ALIGN=CENTER>AUDI<TD ALIGN=CENTER>7800</TD><TD ALIGN=CENTER><FONT COLOR="RED">[b]15600[/b]</FONT></TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>8950</TD><TD ALIGN=CENTER> </TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>8900</TD><TD ALIGN=CENTER> </TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>14000</TD><TD ALIGN=CENTER> </TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>18940</TD><TD ALIGN=CENTER> </TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>14000</TD><TD ALIGN=CENTER> </TD>
</TR>

<TR>
<TD ALIGN=CENTER> </TD><TD ALIGN=CENTER>BMW<TD ALIGN=CENTER>15600</TD><TD ALIGN=CENTER> </TD>
</TR>


<!--- Identifying a Report Component in a WebFOCUS Style Sheet ---?
</TABLE>

</BODY>

</HTML>


Has anyone ever done this in their reports?

Thank you.



Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
October 25, 2019, 10:42 PM
Hallway

Are you saying that you would like the final output to look like this:

COUNTRY CAR SALES SALES2
W GERMANY AUDI 7800 15600
BMW 8950
BMW 8900
BMW 14000
BMW 18940
BMW 14000
BMW 15600

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


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
October 25, 2019, 11:07 PM
Tomsweb
yes


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
October 25, 2019, 11:32 PM
Hallway
It's an ugly hack, but...
  
TABLE FILE CAR
SUM LST.SALES WITHIN COUNTRY AS 'LSTSALES'
BY COUNTRY
PRINT SALES
BY COUNTRY
BY CAR
WHERE COUNTRY EQ 'W GERMANY';
ON TABLE HOLD AS TEMP
ON TABLE SET ASNAMES ON
ON TABLE SET PAGE-NUM OFF
END
-RUN

TABLE FILE TEMP
PRINT COMPUTE CNTR/I7=LAST CNTR+1; NOPRINT
    CAR
    SALES
    COMPUTE SALES2/I11 MISSING ON = IF CNTR EQ 1 THEN LSTSALES ELSE MISSING;
BY COUNTRY
BY CAR NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET NODATA ''
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=SALES2, COLOR=RED, $
ENDSTYLE
END

🤣


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
October 27, 2019, 08:15 PM
StuBouyer
If you are using 8.2x then the analytic functions can help

  
TABLE FILE CAR
SUM 
     COMPUTE SALES2/I6 = PARTITION_AGGR(LST.SALES, COUNTRY, B, E, LST);
BY  CAR.ORIGIN.COUNTRY
PRINT 
     CAR.BODY.SALES
     COMPUTE SALES3/I6 = PARTITION_AGGR(LST.SALES, COUNTRY, B, E, LST);
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR
WHERE COUNTRY EQ 'W GERMANY';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
TYPE=REPORT,
     COLUMN=N1,
     SEQUENCE=1,
$
TYPE=REPORT,
     COLUMN=N2,
     SEQUENCE=5,
$
TYPE=REPORT,
     COLUMN=N3,
     SEQUENCE=2,
$
TYPE=REPORT,
     COLUMN=N4,
     SEQUENCE=3,
$
TYPE=REPORT,
     COLUMN=N5,
     SEQUENCE=4,
$
ENDSTYLE
END


Cheers

Stu


WebFOCUS 8.2.03 (8.2.06 in testing)