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     Reporting joined records on rows below?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Reporting joined records on rows below?
 Login/Join
 
Platinum Member
posted
Hi,

I have a request heading my way that I am not sure is achievable. However I am new to WebFocus and am hoping that something may exist to help me out!

We have 2 identically structured files. The first file contains information of a primary stock count, and is keyed by product. The 2nd file contains many records per product and relates to the 2nd, 3rd, 4th etc stock counts.

So what they are looking for is something like this:

PRODUCT___STOCK_____COUNT_NO_____FILE

ABCDE_____00100_____1____________FILEA
ABCDE_____00099_____2____________FILEB
ABCDE_____00102_____3____________FILEB
ABCDE_____00101_____4____________FILEB

So only data from File A to be on row 1 and the following rows to contain data from File B.

The way I would expect the report to work with my current WebFocus experience would be

PRODUCT___FILEA_STOCK_____FILEB_STOCK

ABCDE_____00100___________00099______
ABCDE_____00100___________00102______
ABCDE_____00100___________00101______

But this is not what is wanted!!

Having a think about it, the best solution I can think of is to enquire FileA with the product and output the record structure to a work file. Then to enquire FileB with the product, append the resulting record structure to the work file and finally to run the report over the work file. Can I append to a file in WebFocus?

Well, I hope you guys can understand my jabbering and perhaps I have answered my own question, but any input would be gratefully received!!

Thanks Mark.


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
Expert
posted Hide Post
Hi Mark,

If your files have exactly the same column names then you could use the MORE syntax -

TABLE FILE FILEA
SUM STOCK
BY PRODUCT
MORE FILEB
END

If they do not have the same column names then a bit of pre-processing would be required.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
For an example, I have mimiced your files using the GGSALES table -

TABLE FILE GGSALES
SUM UNITS
COMPUTE CNT/I3 = 1;
BY PRODUCT
BY STCD
WHERE STCD EQ 'R1100'
ON TABLE HOLD AS FILEA FORMAT FOCUS INDEX PRODUCT
END

TABLE FILE GGSALES
SUM UNITS
COMPUTE CNT/I3 = IF LAST CNT EQ '' OR PRODUCT NE LAST PRODUCT THEN 2 ELSE LAST CNT + 1;
BY PRODUCT
BY STCD
WHERE STCD NE 'R1100'
ON TABLE HOLD AS FILEB FORMAT FOCUS INDEX PRODUCT
END
-RUN

TABLE FILE FILEA
SUM UNITS
 BY PRODUCT
 BY CNT
MORE
FILE FILEB
END


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
Thanks Tony, I will give that a go and will let you know how I get on.

That Count coding also answers what my next question was going to be!!

Cheers!!


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report 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     Reporting joined records on rows below?

Copyright © 1996-2020 Information Builders