Hi all,

I need some help joining to some files where there are going to be many childs per parent on each file and I am sure this problem must have been tackled many times before.

My driving file, DRIVE, has one record per key of PERIOD and CUSTOMER.

I need to join DRIVE to an ORDERS file and an INVOICES file and a RETURNS file. There will be many records on each of these files for each PERIOD and CUSTOMER, but I also need to cater for the fact that there may be no returns for example for some customers, so I need to use SET ALL = PASS.

The final objective is to have one file that I can then summarise using an across table on PERIOD, by Customer, summarising total units orders, invoiced and returned.

I initially started of with something like this, but soon realised that this was not working.

(To keep things a bit simpler I will only work with the first two files I need to join to, as the problem exists when joining to two already.)

SET ALL = PASS

JOIN
DRIVE.PERIOD AND DRIVE.CUSTOMER TO ALL
ORDERS.PERIOD AND ORDERS.CUSTOMER IN ORDERS AS J0
END

JOIN
DRIVE.PERIOD AND DRIVE.CUSTOMER TO ALL
INVOICES.PERIOD AND INVOICES.CUSTOMER IN INVOICES AS J1
END

When joining to the first file, ORDERS, all is fine, but then as I had sort of expected the second join to INVOICES goes a bit funny – either the first set of ORDER data is duplicated again for every record on the INVOICES file, or if the ALL is removed I only pick up the first record from the INVOICES file.

My post is therefore to ask what the best way of tackling this scenario is?

I would prefer to avoid having to summarise the ORDERS and INVOICES files before the join to DRIVE, but perhaps this is unavoidable?

Or perhaps a MATCH is better suited; if so I could really do with some help as what I have tried with MATCH is not working as expected!

I would really like to get this working the best way possible, as many other reports are going to be based on this first one!

Many thanks as always
Mark

Example of the data I have and what I want to achieve:

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

Ok, perhaps writing it all out for the above post made things a bit clearer for me, but I decided to try summarising each file (ORDERS, INVOICES and RETURNS) first and have then done a one to one join from DRIVE to each of the new summarised files and this has worked fine, but I would still like to know if this is the only way around joining to many one-to-many files or if there is a much better solution!

Cheers

If you just need the summary values, a MATCH of the following form will pull together the data from which to produce the Final report.

MATCH FILE DRIVE
COUNT ENTRIES NOPRINT
BY CUSTOMER BY PERIOD
RUN
FILE ORDERS
SUM ORDERED
BY CUSTOMER BY PERIOD
AFTER MATCH HOLD OLD
RUN
FILE INVOICES
SUM INVOICED
BY CUSTOMER BY PERIOD
AFTER MATCH HOLD OLD
RUN
FILE RETURNS
SUM RETURNED
BY CUSTOMER BY PERIOD
AFTER MATCH HOLD OLD
END
?F HOLD

Hi Jack,

I have coded that into my program but currently it is returning loads of lines. It appears to be listing all records, from PERIODS and CUSTOMERS that aren't in DRIVE. I was expecting to only see the same number of records as exist in DRIVE?

Am I correct in thinking that with a MATCH the BY fields are how we are telling the files how to join together? So link DRIVE to ORDERS on PERIOD and CUSTOMER?

What does the 'COUNT ENTRIE' line do? Is it neccesary?

Also, in your example you have listed 'BY CUSTOMER BY PERIOD', but my keys are the other way around so I have reversed these but it still returns way too much data.

Thanks for input...

mark
since jack has probably gone home for this long holiday weekend (like a normal person), i'll try to pinch hit for him..
COUNTRY something NOPRINT
is a clever way of getting you just the extract of CUSTOMER and PERIOD you want, without jack's having to explain more complex things to you at this point. Trust him. You can take the NOPRINT off if you like, and see what happens. its ok.
There's just no way that you can get more than the orginal file length with this code, no way.
To get the Union of all, you would have to be saying
ON TABLE HOLD OLD-OR-NEW
but jack's code says
ON TABLE HOLD OLD
which is exactly designed to give you only the records in DRIVE, the first (or 'old') of the two files being joined, regardless of whats in the second (or 'new') file.
Try this Match 1 step at a time, and look at the results, the record counts.
You can reverse the fields, sure, no problem.
-s

Mark

this might be a stupid question but what is in the real world the file "DRIVE" ?
I see a period and a customer code, but these fields are available in the other files too.

Are you trying to create in fact a union selection and is this DRIVE just a helpfile to get all the customers in all the periods?

What is the original database? If it is SQL you can do this on that platform.

An other solution would be this.

Create 3 selections from the files INVOICES, RETURNS and ORDERS. Append these files (MORE) and then create the reports from that combined set.

Something like this.
(I do suppose the field formats in all datasets are equal.)

SET ASNAMES=ON
TABLE FILE ORDERS
PRINT 
PERIOD 
CUSTOMER 
ORDERED AS 'UNITS'
COMPUTE TYPE/A1='O';
ON TABLE HOLD AS HOLDO 
END
TABLE FILE RETURNS
PRINT 
PERIOD 
CUSTOMER 
RETURNED AS 'UNITS'
COMPUTE TYPE/A1='R';
ON TABLE HOLD AS HOLDR 
END

TABLE FILE INVOICES
PRINT 
PERIOD 
CUSTOMER 
INVOICED AS 'UNITS'
COMPUTE TYPE/A1='I';
ON TABLE HOLD AS HOLDI 
END
-RUN
-* now combine these 3 files to one
TABLE FILE HOLDO
PRINT *
ON TABLE HOLD AS HOLDTOTAL
MORE
FILE HOLDR
MORE
FILE HOLDI
END
-RUN
TABLE FILE HOLDTOTAL
SUM
UNIT
BY PERIOD
BY CUSTOMER
ACROSS TYPE
END

If you need some more descriptive fields for the TYPE you can do a decode and if the customer needs to have an name and address you can join the a customer dataset to the HOLDTOTAL (but then you need to index the customer field and hold this as a FOCUS format file.

Instead of the MORE commands you might want to use the USE functionality. That's in fact a better approach, but the result will be the same.

I hope this helps a bit.

Mark,

Extracting the data 1 step at a time may accomplish your task(BTW, IN PERIODS is missing from your 1st JOIN syntax):

  
JOIN PERIOD AND CUSTOMER IN DRIVE TO ALL PERIOD AND CUSTOMER IN ORDERS AS J1
-RUN
TABLE FILE DRIVE
SUM
  ORDERED_UNITS
 BY PERIOD
 BY CUSTOMER
  ON TABLE HOLD AS HOLD1
END
-RUN
JOIN PERIOD AND CUSTOMER IN HOLD1 TO ALL PERIOD AND CUSTOMER IN INVOICES AS J2
-RUN
TABLE FILE HOLD1
SUM
  INVOICED_UNITS
 BY PERIOD
 BY CUSTOMER
PRINT
  ORDERED_UNITS
 BY PERIOD
 BY CUSTOMER
  ON TABLE HOLD AS HOLD2
END
-RUN
JOIN PERIOD AND CUSTOMER IN HOLD2 TO ALL PERIOD AND CUSTOMER IN RETURNS AS J3
-RUN
TABLE FILE HOLD2
SUM
  RETURNED_UNITS
 BY PERIOD
 BY CUSTOMER
PRINT
  ORDERED_UNITS
  INVOICED_UNITS
 BY PERIOD
 BY CUSTOMER
  ON TABLE HOLD AS HOLD3
END
-RUN
TABLE FILE HOLD3 PRINT *
END
-EXIT

Hope this helps...

Tom

Hi all,

First, thanks again for your all your inputs!

Frank:

The reason I am using DRIVE is that for example there may be no records at all for the PERIOD / CUSTOMER on ORDERS or INVOICES, only in RETURNS, therefore I wanted to make sure I wasn’t going to loose anyone!
My original plan was to join ORDERS to INVOICES to RETURNS, but then any PERIODS / CUSTOMERS not existing in ORDERS would have been lost. By first building DRIVE with the combination of PERIODS and CUSTOMERS the user is enquiring on I know that everything would be included. I hope that makes sense?!

As for appending the three data sets together, this is an idea I hadn't thought of and could be one to try out, but not sure how quick it would be though? I guess I would have to perform selections on each file first to reduce the number of records being appended, but doing individual queries on each dataset was something I was hoping to avoid.


Tom,

Yes you are right, I had missed the 'IN PERIODS' to my first example.
As for your suggestion, I had already started to code in this method after making the first post and whilst it does work fine it is a bit long winded compared to the MATCH. which brings me onto...


Susannah / Jack,

I went back to the MATCH code and as suggested I broke it down to first match one file only, by one key (PERIOD), which I got to work fine. So I then tried to summarise to the next level, CUSTOMER, and this is where it went wrong. After some time scratching my head I found the solution and it was all my fault....the field names actually differ between the DRIVE file and the ORDERS/INVOICE/RETURN files. In DRIVE, CUSTOMER was CUSTOMER, but in the other files it was actually called PARENT. (I hadn't translated this to my post as I was trying to keep everything as clear as possible!)
Because I had been initially joining the files together the difference in name hadn't been an issue, but with MATCH I found this was required. So I simply modified my 'BY PARENT' to 'BY PARENT AS CUSTOMER' and it works delightfully!! Much easier than having to individually join to each file and summarise and the whole lot is being done in one chunk and it works great.

So many thanks to you all and I think I will be using MATCH quite a bit more, it certainly seems pretty powerful.

It's not easy to forecast the performance, but since the 3 individual selections only use one table it will be quicker than a request on two or more joined tables.
How is the table DRIVE created? Is that a real table or is it already the result of a view on more than one table?

Hi Frank,


Sure, I appreciate that the queries on the 3 files should be quicker than if they were joined together.

I create DRIVE at the start of the program using a Cartesian Product or what I think is known here as the McGyver technique?

The user can select up to a maximum of 12 periods, so I select these from a PERIOD file. The user can also select a bunch of customers either by providing a list or from the customer's parent number. Either way I extract a list of customers from the customer file. I then join these together using a dummy field and this results in my DRIVE table containing a record for each PERIOD/CUSTOMER, regardless of any orders/invoices and returns.

Mark --

So you use DRIVE to assure that every customer nominated by the user will appear in the summary report, even if the customer has no activity for all the selected periods.

For efficiency, the MATCH FILE's data sources should not involve Joins to files held locally in WF -- instead you should explicitly screen on the lists of customer and period key values, as shown below, so that the restriction can be passed through to the DB server.

-* assuming 'custlist' is a SAVE file of customer keys,
-* and 'periods' is a SAVE file of period keys
MATCH FILE DRIVE
  COUNT ENTRIES NOPRINT
    BY CUSTOMER AS CUSTOMER
RUN
FILE orders
  SUM ORDERED
    BY CUSTOMER AS CUSTOMER
    BY PERIOD   AS PERIOD
  WHERE CUSTOMER IN FILE 'custlist';
  WHERE PERIOD IN FILE 'periods';
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE invoices
  SUM INVOICED
    BY CUSTOMER AS CUSTOMER
    BY PERIOD   AS PERIOD
  WHERE CUSTOMER IN FILE 'custlist';
  WHERE PERIOD IN FILE 'periods';
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE returns
  SUM RETURNED
    BY CUSTOMER AS CUSTOMER
    BY PERIOD   AS PERIOD
  WHERE CUSTOMER IN FILE 'custlist';
  WHERE PERIOD IN FILE 'periods';
AFTER MATCH HOLD OLD-OR-NEW
END

I sorted BY CUSTOMER BY PERIOD, in order to merge with DRIVE. You can reverse the order in the TABLE that later produces the formatted output from the HOLD file.

OLD-OR-NEW is required in the last two merges, because the "new" matrix may contain periods absent from the "old" for a given customer. Either OLD or OLD-OR-NEW would work in the first merge, since the comparison there is on the minimal-length common key, which is just CUSTOMER, and we are assured that any value in "new" also appears in "old".

Hi Jack,

My report was running ok without the need for these additional WHEREs, but it was quite slow when summarising lots of data!

I then tried to implement what you had suggested and it was so much quicker! I had a bit of difficulty at first, where I was getting an error when working with the IN FILE command. However I found this very useful post from Alan B which explained that I would need to add FORMAT ALPHA to my hold files and that they could not exceed 16k bytes. Once this once done all was good.

So, once again thanks for your the help

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

quote:

... which explained that I would need to add FORMAT ALPHA to my hold files

or use SAVE (rather then HOLD), as the comment in my code indicated.

You're welcome
You're welcome

My word, looks like my ninja editting skills need some practice

Aha, I hadn't picked up on the SAVE file bit.

Cheers