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     [CLOSED]MATCH Values Become Zero

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]MATCH Values Become Zero
 Login/Join
 
Silver Member
posted
Hello,

I am having an issue with my MATCH code. When I run it, it works, but it spits out zeros for half my values. Below is the code.

MATCH FILE TWENTYFIFTEEN
SUM
ITEMS
BY LOWEST YEAR
BY LOWEST PERIOD
BY LOWEST WEEK
BY LOWEST DAY
BY LOWEST DATE
BY STARTWEEK
BY ENDWEEK
RUN
FILE TWENTYSIXTEEN
SUM
ITEMS
SECONDARY_TOTAL
AVERAGE
BY LOWEST YEAR
BY LOWEST PERIOD
BY LOWEST WEEK
BY LOWEST DAY
BY LOWEST DATE
BY STARTWEEK
BY ENDWEEK
AFTER MATCH HOLD AS MHOLD OLD-OR-NEW
END

TABLE FILE MHOLD
PRINT
ITEMS
SECONDARY_TOTAL
AVERAGE
BY LOWEST YEAR
BY LOWEST PERIOD
BY LOWEST WEEK
BY LOWEST DAY
BY LOWEST DATE
BY STARTWEEK
BY ENDWEEK
END

An example of the results I get are below.

Year---Period--Week--Day--Date------StartWeek---EndWeek---Items---Secondary_total--Average
2015--13------52-----1-----20151221--20151019---20151214--86466
------------------------2-----20151222--20151020---20151215--91347
------------------------3-----20151223--20151021---20151216--85005
------------------------6-----20151226--20151024---20151219--72522
2016--1--------1------1-----20151228--20151026---20151221--0-------4264378----------87028.12
------------------------2-----20151229--20151027---20151222--0--------4250093----------86736.59
------------------------3-----20151230--20151028---20151223--0--------4231317----------86353.41
... (Dashes added for readability.)

The issue is under "Items" for 2016. They should not be zeros. They should be 5 digit integers like the ones above the zeros in 2015. Anyone have a clue why this is happening?
I reversed the MATCH and then the 2015 Items would be zeros.

This message has been edited. Last edited by: <Emily McAllister>,


WebFOCUS 8
Windows, All Outputs
 
Posts: 47 | Registered: February 02, 2016Report This Post
Virtuoso
posted Hide Post
The reason is because of your MATCH keys : the BY fields used - they don't match !!!

So your result is a merge of both files together.

MATCH need a common key (not only the field but the values themselve) to have data grouped under the same key, if all of your data have different key values so it's just a merge without any grouping.

It display 0 under 2016 ITEMS because it's the only common SUM field from both files and because they don't MATCH 2015

Why are you using MATCH when both files won't have any common key values ?

This message has been edited. Last edited by: MartinY,


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
Silver Member
posted Hide Post
The BY fields do match. It is the items after SUM that don't match.
I am not trying to MATCH. I am trying to merge 2 tables into one. Like a UNION in sql code.


WebFOCUS 8
Windows, All Outputs
 
Posts: 47 | Registered: February 02, 2016Report This Post
Expert
posted Hide Post
After your MATCH and before your TABLE request, issue a ?F MHOLD to see the layout of the hold file and why the ITEMS values in your second file are shown as blanks.

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
DEFINE FILE MHOLD
  ITEMS/I9 = E08 + E09;
END


Will sort out your issue but I would urge you to understand why.

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
Virtuoso
posted Hide Post
Using FOCUS MATCH command, you are Matching the files not merging

To merge two (or more files) together, process as following:
TABLE FILE TWENTYFIFTEEN
SUM ITEMS
    COMPUTE SECONDARY_TOTAL /P8 = 0;
    COMPUTE AVERAGE /P8.2 = 0;
BY LOWEST YEAR
BY LOWEST PERIOD
BY LOWEST WEEK
BY LOWEST DAY
BY LOWEST DATE
BY STARTWEEK
BY ENDWEEK
ON TABLE HOLD AS TMP1 FORMAT FOCUS
END
-RUN

TABLE FILE TWENTYSIXTEEN
SUM ITEMS
    SECONDARY_TOTAL
    AVERAGE
BY LOWEST YEAR
BY LOWEST PERIOD
BY LOWEST WEEK
BY LOWEST DAY
BY LOWEST DATE
BY STARTWEEK
BY ENDWEEK
MORE 
FILE TMP1
END
-RUN


Merged files must have the same number, name and format of columns to merge reason of the COMPUTE in TMP1 file. I don't know your format so I've put a sample


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
It's a name conflict.

You have two verb objects (non-sortkey columns) named ITEMS -- one in the first leg of the MATCH and one in the second. All the rows from TWENTYFIFTEEN contribute to the first one, and those from TWENTYSIXTEEN to the second.

Later, when your TABLE request references ITEMS, it gets only the first of the two columns. Hence the value for rows pulled (solely) from TWENTYSIXTEEN is zero.

I would rename the two columns, and define or COMPUTE their sum...

MATCH FILE TWENTYFIFTEEN
SUM
  ITEMS AS ITEMS2015
BY LOWEST YEAR
etc
RUN
FILE TWENTYSIXTEEN
SUM
  ITEMS AS ITEMS2016
  ...
BY LOWEST YEAR
etc.
AFTER MATCH HOLD AS MHOLD OLD-OR-NEW
END

TABLE FILE MHOLD
SUM
COMPUTE ITEMS/I9=ITEMS2015+ITEMS2016;
...
BY LOWEST YEAR
etc.
END


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
the code you present has two fields named ITEMS but your sample output has only one field.
so there's a disconnect in your example somewhere.
you're sure that the field formats of all the BY fields are identical b/w the two source files, yes?
Just to be clear, MATCH is side-by-side, MORE is top-to-bottom concatenation.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Guru
posted Hide Post
What works for me is that after I do a MATCH where both tables have the same non-sort field is to do a ? HOLD on the output of the MATCH and then using a define of that field with the data of either of the appropriate internal field.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Master
posted Hide Post
Listen to Professor Gross !!!


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report 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     [CLOSED]MATCH Values Become Zero

Copyright © 1996-2020 Information Builders