Hierarchical vs. Parallel linkage

[Matt Ohmes]

Hi all,
I promised I would write up something on the differences between 
Hierarchical and Parallel linkage in Quiz (and QTP), so here we go. (And 
for those of you who know all this already, please forgive my trip down 
memory lane.)

Oh, and I'm assuming no bugs in the product regarding syntax. :-)

Differences between Hierarchical and Parallel linkage in Quiz (and QTP)

In all examples below, we will use 3 files. Assume all are indexed files, 
keyed on Emp_No
File Employees: 5 records;
Emp_No  Emp_Name
1       Bob
2       Mary
3       Pat
4       Kim
5       Sean

File Paychecks: 5 records
Emp_No  Pay_Date    Amt
1       2015-01-01  100     (Bob)
1       2015-01-15  200     (Bob)
2       2015-01-01  300     (Mary)
2       2015-01-15  400     (Mary)
3       2015-01-15  600     (Pat)
Note: 4 Kim and 5 Sean just started and have not been paid yet)

File Dependents: 4 records;
Emp_No  Dep_Name
1       Bob Jr              (Bob's 1st dependent)
1       Bobbie              (Bob's 2nd dependent)
2       Marie               (Mary's dependent)
5       Shawn               (Sean's dependent)
Note: 3 Pat and 4 Kim do not have any dependents

This will be my Report statement for ALL examples:
Report &
    Emp_No of Employees &
    Emp_Name of Employees &
    Pay_Date of Paychecks &
    Amt of Paychecks &
    Dep_Name of Dependents
 
First Hierarchical linkage:
Access Employees &
    Link Emp_No of Employees to Emp_No of Paychecks &
    Link Emp_No of Employees to Emp_No of Dependents

Emp_No  Emp_Name    Pay_Date    Amt     Dep_Name
1       Bob         2015-01-01  100     Bob Jr
1       Bob         2015-01-01  100     Bobbie
1       Bob         2015-01-15  200     Bob Jr
1       Bob         2015-01-15  200     Bobbie
2       Mary        2015-01-01  300     Marie
2       Mary        2015-01-15  400     Marie

Obvious problems. Pat, Kim, and Sean are not showing up at all because no 
files are optional. I'm assuming the use of Optional in hierarchical 
linkage is well understood so I won't go into that discussion here. This 
post is about parallel linkage. :-)

A bigger problem with the report is that paycheck and dependent records 
are being duplicated. Hierarchical linkage is simply wrong for this data; 
the results make no sense. 
 
Using hierarchical linkage for this data is essentially saying, for each 
paycheck an employee has received, read down the list of all their 
dependents. That makes no sense. Just as it would make no sense to say, 
for each dependent an employee has, show me every time they have been 
paid.  The number of paychecks I receive is based on how long I have 
worked for my employeer, not how many dependents I have.  Similarly, the 
number of dependents I have is not based on how long I have worked for my 
employer. 
 
Paychecks and Dependents are both, obviously related to Employees, but 
they are NOT RELATED TO ONE ANOTHER. 

Instead of one of these files "driving" the other, they should be read in 
parallel; i.e. at the same time.  THIS is why we have parallel linkage. 
Parallel linkage is saying, for each employee, show me each of their 
paychecks AND each of their dependents.

Let's try parallel linkage in the Access statement and what that does to 
our report results.

Access Employees &
    Link Emp_No of Employees to Emp_No of Paychecks &
    AND Emp_No of Employees to Emp_No of Dependents 
 
Emp_No  Emp_Name    Pay_Date    Amt     Dep_Name
1       Bob         2015-01-01  100     Bob Jr
1       Bob         2015-01-15  200     Bobbie
2       Mary        2015-01-01  300     Marie
2       Mary        2015-01-15  400
3       Pat         2015-01-15  600
5       Sean                            Shawn

This makes more sense for this data.  Each paycheck is reported once and 
each dependent is reported once. 
Notice, as long as there is at least one record FROM ANY of the parallel 
files you get a record complex returned. Parallel files are, more or less, 
"semi-optional".

However, also notice employee 4 Kim was not reported.  This was because 
Kim did not have any paychecks (yet) or dependents. If you want to see Kim 
on the report as well, make ANY of the parallel files optional; it doesn't 
make any difference which one because of the "semi-optional" nature of 
parallel files.

Access Employees &
    Link Emp_No of Employees to Emp_No of Paychecks &
    AND Emp_No of Employees to Emp_No of Dependents Optional
 
Emp_No  Emp_Name    Pay_Date    Amt     Dep_Name
1       Bob         2015-01-01  100     Bob Jr
1       Bob         2015-01-15  200     Bobbie
2       Mary        2015-01-01  300     Marie
2       Mary        2015-01-15  400
3       Pat         2015-01-15  600
4       Kim
5       Sean                            Shawn

To be honest, parallel linkage is not that common. It is fair to ask, "If 
two files aren't related to one another, why are you writing a report with 
both of them on it?"  But it does have its uses from time to time.
 
I hope that explanation helps. (It was fun writing about good old Quiz 
again after all these years.)
Cheers,
Matt
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20150305/cd6e101f/attachment-0001.htm>