Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Multiple Product
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Multiple Product
 Login/Join
 
Platinum Member
posted
Hi,
i have scenario where i have to filter a data set for multiple entries and show output from that multiple entries.
I am thinking like SQL query where i can count(column) to check multiple entries.
I am having hard time to figure this out in Webfocus.

  
Company   ProductID  Quality level
	
Com1	  100           1         			  
com2      200           2
com2      201           1
com2      202           3
com2      203           4
com3	  300           1
com3	  301	        2


Here com2 and com3 only has multiple ProductId and i have to filter that and then display only those records with QualityLevel 1.

Output format.
  
com2      201           1
com3	  300           1


Can someone please help?
This is just a small part in the whole scenario for me to start

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


WF8202M,Windows 7,8,10
HTM,PDF,EXCEL
 
Posts: 208 | Location: MI | Registered: September 13, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi,

You can try something like this

TABLE FILE QUALITYLEVELTABLE
SUM CNT.DST.PRODUCTID NOPRINT
BY COMPANY
WHERE TOTAL CNT.DST.PRODUCT GT 1;
ON TABLE HOLD AS HLDCOMPANY
END
-RUN

TABLE FILE QUALITYLEVELTABLE
SUM/PRINT
QUALITYLEVEL
BY COMPANY
BY PRODUCTID
WHERE COMPANY IN FILE HLDCOMPANY;
WHERE QUALITYLEVEL EQ 1;
END
-RUN

You indicated you might like a SQL query, in that case:
select company, productid, qualitylevel from qualityleveltable
where company in (select company from qualitylevels group by company having count(distinct productid) > 1) and qualitylevel = 1

Martin.


WebFocus 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 154 | Registered: March 29, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
TABLE FILE ......
COUNT PRODUCT_ID NOPRINT
BY COMPANY
ON TABLE SAVE
END
TABLE FILE .....
PRINT COMPANY PRODUCT_ID QUALITY_LEVEL
IF COMPANY EQ (SAVE)
IF QUALITY_LEVEL EQ 1
END
 
Posts: 142 | Location: Adelaide South Australia | Registered: October 27, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
quote:
NOPRINT

Sorry missed a bit
you need
SET ASNAMES=ON
TABLE FILE .....
COUNT PRODUCT_ID AS 'COUNTER'
BY COMPANY
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT COMPANY
IF COUNTER GT 1
ON TABLE SAVE
END
TABLE FILE ....
PRINT COMPANY PRODUCT_ID QUALITY_LEVEL
IF COMPANY EQ (SAVE)
IF QALITY_LEVEL EQ 1
END

There are other ways to possibly do this in a single pass but I think this three step process may be easier to follow. If the data volume is not huge then run-time will not be an issue.

If you want a more efficient solution just ask again and I or someone else can write some code to produce the same result in less passes.
 
Posts: 142 | Location: Adelaide South Australia | Registered: October 27, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thanks @Martin and @OPALTOSH.
My actual scenario is way more complex.
I will post the scenario as another post,if i gets stopped,so that someone can give me some idea.


WF8202M,Windows 7,8,10
HTM,PDF,EXCEL
 
Posts: 208 | Location: MI | Registered: September 13, 2017Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Multiple Product

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.