Hierarchical vs. Parallel linkage
Richard Witkopp
RWitkopp at phxa.com
Thu Mar 5 18:28:26 CST 2015
Nice, not-a-zero!
Are there still Powerhoose conventions? This would make a good presentation.
I need to re-read this thing about 4 times. I've got a qtp with about 5 parallel legs and 3 hierarchical files down each leg and it doesn't do what it looks like it should do.
From: powerh-l-bounces+rwitkopp=phxa.com at lists.sowder.com [mailto:powerh-l-bounces+rwitkopp=phxa.com at lists.sowder.com] On Behalf Of Matt Ohmes
Sent: Thursday, March 05, 2015 3:57 PM
To: PowerHouse listserver
Subject: Hierarchical vs. Parallel linkage
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
securemail.phxa.com made the following annotations
---------------------------------------------------------------------
NOTICE: The information contained in this e-mail and
any attachments is confidential and may be privileged
or otherwise protected from disclosure.This e-mail is
intended solely for the use of the named addressee.
Any other use, printing, copying, disclosure or
dissemination may be subject to legal restriction. If
you are not the intended recipient, please contact the
sender and delete all copies including any attachments.
---------------------------------------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sowder.com/pipermail/powerh-l/attachments/20150305/67df9801/attachment.htm>
More information about the powerh-l
mailing list