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>,

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,

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.

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

DEFINE FILE MHOLD
  ITEMS/I9 = E08 + E09;
END

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

T

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

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

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.

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.

Listen to Professor Gross !!!