Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Merge Multiple Date Groups into Single Table via Across Statement

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Merge Multiple Date Groups into Single Table via Across Statement
 Login/Join
 
Platinum Member
posted
Hello!

I have a project where the user would like to have three different time periods as columns in a single table. Does anyone have an idea how to do this?

I am assuming I will need three separate hold files and then a match statement but that hasn't been working for me so far.

The table would like something like this:

Previous Year | Current Year | Current Month
8 3 1
3 4 2
5 1 5
1 2 1

This message has been edited. Last edited by: Brandon Andrathy,


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Virtuoso
posted Hide Post
If you have your data properly stored by date you can do the following

DEFINE FILE GGSALES
YEAR /YY = DATE;
MONTH /Mt = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS
BY PRODUCT
ACROSS YEAR
ACROSS MONTH
END


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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
Hey Martin,

Thank you very much for responding to me so quickly!

I thought that method would work as well but then I get an error message when I try to put the two dates in two different Across statements.

Error message is: A column has been specified more than once in the order by list.


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Virtuoso
posted Hide Post
Brandon

Post your code so we can see it. From the sound of the error message your field name must be duplicated.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Platinum Member
posted Hide Post
Hey Chuck,

Here is the code that I am working with

DEFINE FILE _DST_STAR
PREV_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
CUR_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
END
TABLE FILE _DST_STAR
SUM
NUM
BY MASTERIPA
ACROSS PREV_YEAR
ACROSS CUR_YEAR
END
-EXIT


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Virtuoso
posted Hide Post
I'm not sure to understand when you say that you need to put the two dates into two ACROSS.
The ACROSS verb is similar to a BY, but it's become a "BY Column" instead of "By Row" as a regular BY field.
Think of Excel Pivot table. Rows = BY, Column = ACROSS, Values = SUM/PRINT

You may have an issue with your data source and/or structure.

It may be a good idea to share your code and data structure (master file).
To do so, use the code tag. Last one on the ribbon
</>


Depending of your data structure you may not need three separate hold files to become one. Can't say if it's going to be or not the case.

What is exactly your Previous year, Current Year & Current month ?
What is the difference between Current Year and Current Month ? Is Current Year all from Jan up to now where Current Month it's only Oct 2018 ?


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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
No need to have two columns defined
DEFINE FILE _DST_STAR
-*PREV_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
-*CUR_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
END
TABLE FILE _DST_STAR
SUM NUM
BY MASTERIPA
-*ACROSS PREV_YEAR
-*ACROSS CUR_YEAR
ACROSS YEAR
END
-EXIT


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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
Brandon

The way you have done your DEFINE you are referencing the same field and causing the underlying error you see


PREV_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;
CUR_YEAR/YYMD = _DST_STAR.DST_DIMDATE.DATECALENDARKEY;


Follow Martin's advice.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Platinum Member
posted Hide Post
Ok,

I'm getting there!

Martin, thank you very much for helping me troubleshoot this!

I am very very close!

So I searched through the forums and found out that Universal Concatenation might be a good bet here. So I took what you said and made three hold files.

I need to identify three time periods using months for each one.

1: Previous Year based on month
2. Current Year: current 12 month period
3. Current YTD: current month

I have this part solved and I think I am good!

This is what I have so far.

 

TABLE FILE _DST_STAR
SUM
     NUM
BY _DST_STAR.DST_DIMDATE.DATECALENDARKEY 
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2017/12/31'
ON TABLE HOLD AS 'PREV_YEAR'
END

TABLE FILE _DST_STAR
SUM
     NUM
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
ON TABLE HOLD AS 'MONTH_TREND'
END

TABLE FILE _DST_STAR
SUM
     NUM
	 ADHERENCE
	 DEN
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
ON TABLE HOLD AS 'YTD_TREND'
END

TABLE FILE PREV_YEAR
HEADING
"Yup Yup"
""
SUM
	NUM
ACROSS DATECALENDARKEY
MORE
FILE MONTH_TREND
MORE
FILE YTD_TREND
END
-EXIT

 


However, there are two additional things I want to do here:

1. Apply individual heading names to each of the three groupings
2. Current YTD requires two additional fields that are not available in the other two groupings (Adherence, and DEN)

Does anyone have idea of how to address these two obstacles?

This message has been edited. Last edited by: Brandon Andrathy,


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Virtuoso
posted Hide Post
I don't know you field format so I assume a format.
Something such as this may do the trick to answer Q2
TABLE FILE _DST_STAR
SUM NUM
    COMPUTE ADHERENCE /P8 MISSING ON = MISSING;
    COMPUTE DEN       /P8 MISSING ON = MISSING;
BY _DST_STAR.DST_DIMDATE.DATECALENDARKEY 
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2017/12/31'
ON TABLE HOLD AS 'PREV_YEAR'
END

TABLE FILE _DST_STAR
SUM NUM
    COMPUTE ADHERENCE /P8 MISSING ON = MISSING;
    COMPUTE DEN       /P8 MISSING ON = MISSING;
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
ON TABLE HOLD AS 'MONTH_TREND'
END

TABLE FILE _DST_STAR
SUM NUM
    ADHERENCE
    DEN
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
ON TABLE HOLD AS 'YTD_TREND'
END

TABLE FILE PREV_YEAR
HEADING
"Yup Yup"
""
SUM NUM
    ADHERENCE
    DEN
ACROSS DATECALENDARKEY
MORE
FILE MONTH_TREND
MORE
FILE YTD_TREND
END
-EXIT

Need to think about it to answer Q1


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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
Thank you! I think that is def a step in the right direction.

Do you have an idea of how to NOPRINT the columns with MISSING values? I'd like to not have them display at all.


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Virtuoso
posted Hide Post
Brandon

Use a WHERE condition to screen out the MISSING values.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Virtuoso
posted Hide Post
A WHERE clause will not only omit the column with missing values, it will omit the whole data row.

Not the most elegant way but it works
DEFINE FILE GGSALES
YEAR  /YY  = DATE;
MONTH /Mt  = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS AS 'COLVAL'
BY PRODUCT
BY YEAR
BY MONTH
BY TOTAL COMPUTE COLID  /I1   = 1;
BY TOTAL COMPUTE COLTXT /A10V = 'Sales';
WHERE YEAR EQ '1996';
ON TABLE HOLD AS TMP1
END
-RUN

TABLE FILE GGSALES
SUM DOLLARS AS 'COLVAL'
BY PRODUCT
BY YEAR
BY MONTH
BY TOTAL COMPUTE COLID  /I1   = 1;
BY TOTAL COMPUTE COLTXT /A10V = 'Sales';
WHERE YEAR EQ '1997';
ON TABLE HOLD AS TMP2
END
-RUN

TABLE FILE GGSALES
SUM BUDDOLLARS AS 'COLVAL'
BY PRODUCT
BY YEAR
BY MONTH
BY TOTAL COMPUTE COLID  /I1   = 2;
BY TOTAL COMPUTE COLTXT /A10V = 'Budget';
WHERE YEAR EQ '1997';
ON TABLE HOLD AS TMP3
END
-RUN

TABLE FILE TMP1
SUM COLVAL
BY PRODUCT
BY YEAR
BY MONTH
BY COLID
BY COLTXT
ON TABLE HOLD AS MRGFIL
MORE
FILE TMP2
MORE
FILE TMP3
END
-RUN

TABLE FILE MRGFIL
SUM COLVAL/D8 AS ''
BY PRODUCT    AS 'Product'
ACROSS YEAR   AS ''
ACROSS MONTH  AS ''
ACROSS COLID  NOPRINT
ACROSS COLTXT AS ''
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
Thanks Martin and Chuck for your input!

Martin, wow that works perfectly.

I'm still having difficulty with hiding the other columns that have MISSING values with the WHERE Clause. I have put it in the merged/universal concatenation file but the values are still displaying and it completely omits the first hold file.

 

TABLE FILE _DST_STAR
SUM
     NUM
	 COMPUTE ADHERENCE/D12.2% MISSING ON = MISSING;
	 COMPUTE DEN/I11 MISSING ON = MISSING;
BY _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 1;
BY TOTAL COMPUTE COLTXT/A20V = 'Previous Year';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2017/12/31'
WHERE DEN NE MISSING
ON TABLE HOLD AS 'PREV_YEAR'
END

TABLE FILE _DST_STAR
SUM
     NUM
	 COMPUTE ADHERENCE/D12.2% MISSING ON = MISSING;
	 COMPUTE DEN/I11 MISSING ON = MISSING;
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 2;
BY TOTAL COMPUTE COLTXT/A20V = 'Monthly Trend';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/04/30'
ON TABLE HOLD AS 'MONTH_TREND'
END

TABLE FILE _DST_STAR
SUM
     NUM
	 COMPUTE ADHERENCE/D12.2% = NUM/DEN;
	 DEN
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 3;
BY TOTAL COMPUTE COLTXT/A20V = 'Current YTD';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/10/02'
ON TABLE HOLD AS 'YTD_TREND'
END

TABLE FILE PREV_YEAR
HEADING
""
SUM
	NUM
	DEN
	ADHERENCE
ACROSS COLID NOPRINT
ACROSS COLTXT AS ''
ACROSS DATECALENDARKEY AS ''
BY MEASUREFULLNAME
WHERE DEN NE MISSING
-*WHERE ADHERENCE NE MISSING
MORE
FILE MONTH_TREND
MORE
FILE YTD_TREND
END
-EXIT 


Should I be using a different WHERE clause or placing it somewhere differently?

This message has been edited. Last edited by: Brandon Andrathy,


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Virtuoso
posted Hide Post
quote:

Should I be using a different WHERE clause or placing it somewhere differently?

As I previously stated :
quote:

A WHERE clause will not only omit the column with missing values, it will omit the whole data row.

Look at my last sample code for the technic that I've used.


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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
Hey Martin,

Thanks for the quick reply. Your code definitely makes sense, but I want to display three SUM fields in the last Col ID, and only have one SUM field in the 1st column. Do you have any idea of how to accomplish that?


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Platinum Member
posted Hide Post
I'm gonna create a new thread just for this request since it's outside the scope of what I have now accomplished. Thanks for all of your help!!!


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Virtuoso
posted Hide Post
quote:
but I want to display three SUM fields in the last Col ID

Same technic, just repeat it for a third one


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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
Martin, it finally clicked!

Holy cow, so cool. Thanks a lot. I will post my code when I get this working 100%


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Platinum Member
posted Hide Post
Here's the code I came up with. I created another variable similar to COLID for the sum fields called SUMID. Then those get organized under each COLID Smiler.

 TABLE FILE _DST_STAR
SUM
     COMPUTE COLVAL/D12 = NUM;
BY _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 1;
BY TOTAL COMPUTE SUMID/I1 = 1;
BY TOTAL COMPUTE COLTXT/A20V = 'Previous Year';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2017/12/31'
ON TABLE HOLD AS 'PREV_YEAR'
END

TABLE FILE _DST_STAR
SUM
     COMPUTE COLVAL/D12 = NUM;
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 2;
BY TOTAL COMPUTE SUMID/I1 = 2;
BY TOTAL COMPUTE COLTXT/A20V = 'Monthly Trend';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/04/30'
ON TABLE HOLD AS 'MONTH_TREND'
END

TABLE FILE _DST_STAR
SUM
	COMPUTE COLVAL/D12 = NUM;
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 3;
BY TOTAL COMPUTE SUMID/I1 = 3;
BY TOTAL COMPUTE COLTXT/A20V = 'Current YTD';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/10/02'
ON TABLE HOLD AS 'YTD_TREND_NUM'
END

TABLE FILE _DST_STAR
SUM
	COMPUTE COLVAL/D12 = DEN;
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 3;
BY TOTAL COMPUTE SUMID/I1 = 4;
BY TOTAL COMPUTE COLTXT/A20V = 'Current YTD';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/10/02'
ON TABLE HOLD AS 'YTD_TREND_DEN'
END

TABLE FILE _DST_STAR
SUM
	COMPUTE COLVAL/D12 = NUM/DEN*100;
BY  _DST_STAR.DST_DIMDATE.DATECALENDARKEY
BY _DST_STAR.DST_DIMQUALITYMEASURE.MEASUREFULLNAME
BY TOTAL COMPUTE COLID/I1 = 3;
BY TOTAL COMPUTE SUMID/I1 = 5;
BY TOTAL COMPUTE COLTXT/A20V = 'Current YTD';
WHERE _DST_STAR.DST_DIMDATE.DATECALENDARKEY  EQ '2018/10/02'
ON TABLE HOLD AS 'YTD_TREND_ADH'
END 


Now all I gotta do is play around with the formatting of the single sum field but I feel comfortable manipulating an Alphanumeric format.

Thanks so much. Didn't think this could be done!!


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Merge Multiple Date Groups into Single Table via Across Statement

Copyright © 1996-2020 Information Builders