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] Display based on minimum of a column

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Display based on minimum of a column
 Login/Join
 
Platinum Member
posted
Hi All,

I am stuck while i try to implement logic of a report using the data i have.

  
Year	Ref_no	   Product_Id	Priority_Id	Sale Count
2017	17003014	13001	11	1
	17003278	35001	19	1
	17003332	50000	27	1
	17003679	50000	27	1
	17004196	26001	13	1
	17004971	23007	6	1
	17008132	13001	11	1
				        2
		        23007	6	1
				        2
	17011293	26001	13	0
				1
	201702418	35001	19	1
	201707530	54001	27	1
	201707568	13002	4	0
				1
	201707811	50000	27	1
	210013621	54002	22	0
				1
	210014246	54002	22	0
				1
	220011398	54002	22	0
				1
	330003290	23003	6	1
	610001785	11002	2	0



This is how the data is for an year.
For reference number with multiple product_Id i need to display only product_Id details with lowest Priority-Id.
I have to use this data as a HOLD file for rest of the report logic.

Please give some suggestions.

This message has been edited. Last edited by: FP Mod Chuck,


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
 
Posts: 229 | Location: MI | Registered: September 13, 2017Report This Post
Virtuoso
posted Hide Post
Using the data you show:
for Ref_no 17008132
you want to show only Product_Id 23007 because its Priority_Id is 6? And all Sale Count?


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
Virtuoso
posted Hide Post
Sounds like you need:
BY LOWEST 1 Priority_Id


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Platinum Member
posted Hide Post
Hi srajeevan,
I took below sample data and created a master file called Prod_Ref

 Ref_no	Product_Id	Priority_Id	SaleCount
17008132	13001	11	1
17008132	13001	11	2
17008132	23007	6	1
17008132	23007	6	2
17003014	13001	11	1
17003278	35001	19	1
17003332	50000	27	1
17003679	50000	27	1
17004196	26001	13	1
17004971	23007	6	1 


Below is the WF code to get the min priority prod
 TABLE FILE PROD_REF
SUM
MIN.PRIORITY_ID WITHIN  PROD_REF.PROD_REF.REF_NO AS 'MIN'
BY  PROD_REF.PROD_REF.REF_NO
BY  PROD_REF.PROD_REF.PRODUCT_ID
BY  PROD_REF.PROD_REF.PRIORITY_ID
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS MIN
END

DEFINE FILE MIN
FLAG/A2 = IF(PRIORITY_ID EQ MIN) THEN 'Y' ELSE 'N';
END 
TABLE FILE MIN
PRINT 
FLAG
BY  REF_NO
BY  PRODUCT_ID
BY  PRIORITY_ID
WHERE  FLAG EQ 'Y'
END 


Report output:-

  REF_NO PRODUCT_ID PRIORITY_ID FLAG 
17003014 13001 11 Y 
17003278 35001 19 Y 
17003332 50000 27 Y 
17003679 50000 27 Y 
17004196 26001 13 Y 
17004971 23007 6 Y 
17008132 23007 6 Y 


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Danny-SRL:
Using the data you show:
for Ref_no 17008132
you want to show only Product_Id 23007 because its Priority_Id is 6? And all Sale Count?


Yes @Danny-SRL.I want to show only Product_Id 23007 with its Sale Count.


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
 
Posts: 229 | Location: MI | Registered: September 13, 2017Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Wep5622:
Sounds like you need:
BY LOWEST 1 Priority_Id


@Wep5622..
 BY LOWEST 1 Priority_Id 
wont work because Priority_Id is sorting based on Product_Id.
Ex: for Ref_no 17008132 two Product_Id are there..so When i give
 BY LOWEST 1 Priority_Id  
,for the Product_Id 13001 lowest is 11 itself and for 23007 lowest priority_id id 6..so both will display.


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
 
Posts: 229 | Location: MI | Registered: September 13, 2017Report This Post
Platinum Member
posted Hide Post
@Chaudhary.

You solution is working for me.
Thanks. Smiler Saved my day.


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
 
Posts: 229 | Location: MI | Registered: September 13, 2017Report This Post
Platinum Member
posted Hide Post
quote:
You solution is working for me.
Thanks. Saved my day.

Smiler


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report 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] Display based on minimum of a column

Copyright © 1996-2020 Information Builders