Got stuck again, yay~

So I got this table :

Day     QTY
1       15
2       10
3       20
4       0
...
30      0
31      0

and this table :

Day     QTY
4       35

need to join the two so it comes out like :

Day     QTY
1       15
2       10
3       20
4       35
...
30      0
31      0

anyone know how to do this? Tried using CMD COPY HOLD1 + HOLD2 HOLD3 but it came out :

Day     QTY
1       15
2       10
3       20
4       0
...
30      0
31      0
4       35

thanks~

Yuuta,

Take your combined file and -

TABLE FILE HOLD3
SUM QTY
BY Day
etc. ...

Or you could use -

JOIN CLEAR *
JOIN Day IN HOLD1 TAG T1 TO Day IN HOLD2 TAG T2 AS J1
DEFINE FILE HOLD1
TOT_QTY/I9 = T1.QTY + T2.QTY;
END
TABLE FILE HOLD1
SUM TOT_QTY AS QTY
BY Day
ON TABLE HOLD AS HOLD3
END

and many more different methods, some better than others.

T

What's the difference between the usual JOIN with JOIN CLEAR * ? Sorry, I'm already in the bus ride home, I can only try it tommorow

I already thought of SUMing between the two but, sorry, should've make this more clear, the combined table needs to be used next month as well.

June 1st, table 1:

Day     QTY
1       10

table 2:

Day     QTY
1       15
2       10
3       20
...
30      40
31      35

SUM between the two will result in

Day     QTY
1       25

when I needed the QTY to be 10. Any suggestion? thanks~

Yuuta,

You should use Universal Concatenation or the MORE command.

TABLE FILE DAY1
SUM QTY
BY DAY
MORE
FILE DAY2
END

Presuming you want to report the highest of all quantities encountered for each date:

The report should sort the combined data BY DATE BY QTY then either PRINT the LST.DATE and LST.QTY ('LST.' prints the last instance) or SUM DATE and a DEFINEd field that's an alphabetic representation of QTY (SUM automatically prints the last instance of an alphabetic field in the sort's final BY group).

This should work:

MERGE FILE TABLE1
SUM
QTY AS QTY1
BY DAY
RUN

FILE TABLE2
SUM
QTY AS QTY2
BY DAY
AFTER MATCH HOLD AS FINAL_TABLE OLD-OR-NEW
END
-RUN

DEFINE FILE FINAL_TABLE
QTY/D12.2 = QTY1 + QTY2
END

TABLE FILE FINAL_TABLE
SUM
QTY
BY DAY
END

I would go with Ginny's code - easiest and least amount of code, but as MORE is only valid for FOCUS tables, it would require that both initial tables are held in FOCUS format.

Darin,

That is not true. MORE works for anything.

You are correct. Don't know why I had that stuck in my brain. Hmm. From the Manual:

With universal concatenation, you can retrieve data from unlike data sources in a single request; all data, regardless of source, appears to come from a single file. The MORE phrase can concatenate all types of data sources (such as, FOCUS, DB2, IMS, VSAM), provided they share corresponding fields with the same format. You can use WHERE and IF selection tests in conjunction with MORE. For related information, see Selecting Records for Your Report.

To use MORE, you must divide your request into:

One main request that retrieves the first data source and defines the data fields, sorting criteria, and output format for all data.

Subrequests that define the data sources and fields to be concatenated to the data of the main request. The fields printed and sorted by the main request must exist in each concatenated data source. If they do not, you must create them as virtual fields.

During retrieval, data is gathered from each data source in turn, then all data is sorted and the output formatted as specified in the main request.

How about a match on the three files, using a
BY DAY
BY QTY
and hold old-or-new

MATCH FILE1
PRINT QTY
BY DAY
BY QTY
RUN
FILE2
PRINT QTY
BY DAY
BY QTY
RUN
FILE3
PRINT QTY
BY DAY
BY QTY
AFTER MATCH HOLD AS HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT DAY QTY
END

quote:

What's the difference between the usual JOIN with JOIN CLEAR *

Yuuta,

If you have to ask then it might be time to Read The FOCUS Manual?

As I mentioned earlier, there are many ways to achieve an end result, but you really need to analyse what occurs with your data when using each method and choose which one suits your purpose.

If you still have problems getting the result you need to achieve then perhaps you could explain in deeper detail. For instance if you want the result of QTY for Day 1 to be 10 then why are you summing in the first place and why don't you keep the original file?

T

The manual? The manual's gone Once upon a time there's a manual when my company started using WebFocus but after a few years and a few personel changes the manual's just gone... haven't checked if there's any online version of the manual, just thought of that.

Ok, I'll try to be a more thoughrough this time, sorry all, English isn't my first language I have a hard time finding word to explain things

Actualy, after messing around with it some more I managed to get what I wanted. Took me 3 hold files changing and moving around values but it gets the job done. Haven't try out every single suggestion on this thread but I'll look into it maybe there's an easier solutions to my problem. Thank you all for your help.

On another note, rather than creating a new topic, I seem to be having some trouble with joining two hold files together. Both are created with ON TABLE HOLD AS FORMAT FOCUS and both join field are of the same format(A2). Yet it won't join >.<

My problem if anyone interested :
I got a table that changes value everyday, let's call it TBLLOG

QTY     NAME
10      A
15      B
20      C
25      D

I need to sum and compute etc the whole thing so it become's like this

TODAYDATE    A     B     C     D
15           10    15    20    25

then I need to create a graph on this daily including past dates of the month(starting from the 1st day of month up to today). I figured since the data on TBLLOG is changed everyday I'm going to need to store the whole data somewhere. That's when I thought of using a hold file for it. Let's call it TBLTEMPHOLD

DATE          A     B     C     D   
1             10    etc.
2             15
3             20
4             25
5             15
... up to
30            30
31            25

next month is the same thing all over again but rather than using a new hold file I'm thinking of reusing TBLTEMPHOLD by just changing the values to new values. Just changing, no calculations or anything. I hope this is clear, if there's any confusion just let me know, thanks~

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

The manuals are available online from tech support. Downloads are free, printed copies cost. To answer your question, JOIN CLEAR * clears all previous joins.