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] How to display last 4 records only

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to display last 4 records only
 Login/Join
 
Egon
Gold member
posted
Hi,

This is my first post, so if I'm not doing something correctly, please let me know.

I'm new to WebFocus and I'm currently working on a report that will show the 4 most recent data points in a across for a specific metric but I cannot figure out how to do this. Any help would be greatly appreciated. I've included the code below and what the report looks like.

The current code
 
JOIN
METRIC.METRIC.METRICID IN METRIC TO MULTIPLE METRICSERIES.METRICSERIES.METRICID
IN METRICSERIES TAG J0 AS J0
END
JOIN
J0.METRICSERIES.METRICID AND J0.METRICSERIES.METRICSERIESID IN METRIC
TO MULTIPLE METRICDATA.METRICDATA.METRICID
AND METRICDATA.METRICDATA.METRICSERIESID IN METRICDATA TAG J1 AS J1
END
JOIN
J1.METRICDATA.METRICTIMEFRAMEID IN METRIC TO MULTIPLE
METRICTIMEFRAME.METRICTIMEFRAME.METRICTIMEFRAMEID IN METRICTIMEFRAME TAG J2
AS J2
END

DEFINE FILE METRIC
METRICPERCENT/D5.2%=J1.METRICDATA.METRICDATA * 100;
END

TABLE FILE METRIC
SUM 
     J1.METRICDATA.METRICDATA/P4.1C AS ''
BY  LOWEST METRIC.METRIC.METRICNAME AS ''
ACROSS LOWEST J2.METRICTIMEFRAME.METRICTIMEFRAMEDESCLONG AS ''
WHERE ( METRIC.METRIC.METRICID EQ 113 ) AND ( J1.METRICDATA.UNITID EQ 1 ) AND ( J0.METRICSERIES.METRICSERIESID EQ 418 );
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$ 
ENDSTYLE
END


What the report currently looks like (the table has 5 data points at the moment, but like I said, I only want it to display the 4 most recent data points). Kind of like a reversed retrieval limit.

This message has been edited. Last edited by: <Kathryn Henning>,


WebFocus App Studio 8.1.0.5, Windows 7 64bit.
 
Posts: 65 | Location: Missouri, USA | Registered: March 06, 2014Report This Post
Francis Mariani
Expert
posted Hide Post
ACROSS HIGHEST 4 J2.METRICTIMEFRAME.METRICTIMEFRAMEDESCLONG AS ''


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Egon
Gold member
posted Hide Post
Thank you Francis, I tried that but it did not work. It still retrieved 5 records, plus it changed the display order to show the most recent first, which is not what my users would like to see.


WebFocus App Studio 8.1.0.5, Windows 7 64bit.
 
Posts: 65 | Location: Missouri, USA | Registered: March 06, 2014Report This Post
Francis Mariani
Expert
posted Hide Post
Yes, sorry, the ACROSS HIGHEST will do that, though ACROSS 4 HIGHEST should only show the latest four.

You could add a filter to ensure only the appropriate dimension values are included,

Something like 
WHERE J2.METRICTIMEFRAME.METRICTIMEFRAMEDESCLONG IN ('Fall 2010', 'Fall 2011', 'Fall 2013', 'Fall 2013')

This could be generated by some prior step.

Or you could create a HOLD file containing the Across dimension values that you need. Something like this working example:

TABLE FILE GGORDER
SUM
STORE_CODE NOPRINT
BY HIGHEST 4 STORE_CODE
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HSTORES
END

TABLE FILE GGORDER
SUM
QUANTITY
ACROSS STORE_CODE
WHERE STORE_CODE IN FILE HSTORES
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Dave
Master
posted Hide Post
Egon,

if HIGHEST 4 doesn't work ( because duplicate values ) here's a solution.

TABLE FILE source
SUM fact
    COMPUTE COUNTER/I11 = COUNTER + 1; NOPRINT
BY  dimvalue1
BY  dimvalue2
BY  dimvalue3
WHERE TOTAL COUNTER LE 4
END


This will ensure you only get 4 results based on sort order of the BY-fields. Even if there are duplicates, it will stop at 4 results.

Good luck,
Dave


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Hi Egon and welcome to the WF forum!

The following should help:
  
JOIN
METRIC.METRIC.METRICID IN METRIC TO MULTIPLE METRICSERIES.METRICSERIES.METRICID
IN METRICSERIES TAG J0 AS J0
END
JOIN
J0.METRICSERIES.METRICID AND J0.METRICSERIES.METRICSERIESID IN METRIC
TO MULTIPLE METRICDATA.METRICDATA.METRICID
AND METRICDATA.METRICDATA.METRICSERIESID IN METRICDATA TAG J1 AS J1
END
JOIN
J1.METRICDATA.METRICTIMEFRAMEID IN METRIC TO MULTIPLE
METRICTIMEFRAME.METRICTIMEFRAME.METRICTIMEFRAMEID IN METRICTIMEFRAME TAG J2
AS J2
END

DEFINE FILE METRIC
METRICPERCENT/D5.2%=J1.METRICDATA.METRICDATA * 100;
END
-* Generate a HOLD file with the 4 most recent
TABLE FILE METRIC
SUM 
     J1.METRICDATA.METRICDATA/P4.1C 
BY  LOWEST METRIC.METRIC.METRICNAME 
BY HIGHEST 4 J2.METRIBLE CTIMEFRAME.METRICTIMEFRAMEDESCLONG 
WHERE ( METRIC.METRIC.METRICID EQ 113 ) AND ( J1.METRICDATA.UNITID EQ 1 ) AND ( J0.METRICSERIES.METRICSERIESID EQ 418 );
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD
END
-* Create the report with ACROSS
TABLE FILE HOLD
SUM
METRICDATA
BY METRICNAME 
ACROSS METRICTIMEFRAMEDESCLONG 
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$ 
ENDSTYLE
END


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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Egon
Gold member
posted Hide Post
Francis, thank you, however defining the columns will not work as they are dynamic. I'm going to use Danny's approach of creating the hold file which you also suggested. I just tried it and it works. Dave, luckily there can't be any duplicates so I won't have to incorporate your solution, but I've stored it in my notes for now in case that ever does come up.

Thank you all for finding a solution so quickly!

-Egon


WebFocus App Studio 8.1.0.5, Windows 7 64bit.
 
Posts: 65 | Location: Missouri, USA | Registered: March 06, 2014Report 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] How to display last 4 records only

Copyright © 1996-2020 Information Builders