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
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
August 03, 2016, 11:46 AM
MartinY
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
August 03, 2016, 11:50 AM
RMM
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
August 03, 2016, 11:54 AM
Tony A
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
August 03, 2016, 11:56 AM
Tony A
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
August 03, 2016, 11:57 AM
MartinY
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
August 03, 2016, 12:10 PM
j.gross
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
August 05, 2016, 02:35 PM
susannah
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
August 05, 2016, 02:49 PM
RSquared
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