Comparing dates - Squeeze two records into one.
Edis, Robert
REdis@Blistex.com
Fri, 29 Aug 2003 09:38:48 -0500
"MERGE"
BTW, SQL is no better than Quiz at doing this type of task. Unless you do a self join, and have something to join on, you can't compare one row with another in the same table. You do it in QTP due to the existence of variables (temp items) that can be persistent in the course of the code execution. SQL can achieve this in a cursor but at least in Transact SQL (SQL Server) cursors are so resource intensive they're often not worth the bother. I would use the same method you describe in T-SQL as well as this would provide the best performance.
Blue
-----Original Message-----
From: Seamus Browne [mailto:Seamus.Browne@partage.org]
Sent: Friday, August 29, 2003 9:15 AM
To: powerh-l@cube.swau.edu
Subject: Comparing dates - Squeeze two records into one.
You may find this helpful. I often did.
OK, it is not a one pass trick, but it avoids having to compare data in
one record with data in the next record. IMO, Quiz is not so good at
that.
In the first pass, get the first date leaving the second date blank (0).
In the second pass, get the second date leaving the first date blank
(0).
In the third pass, combine the two records for each employee doing a
KEEP AT EMPLOYEE_NO, using a MAX on both dates so as to save the
significant date and drop the blank (0) date.
Then you can compare the dates within the same record.
;First pass
acc employees
define first-date date = employee_file_date_item
define second-date date = 0
set sub name outfile keep
rep sum employee_no &,
first_date &,
second_date
go
;Second pass
acc the_other_file
define first-date date = 0
define second_date date = second_file_date_item
set sub name outfile APPEND
rep sum employee_no &,
first_date &,
second_date
go
;Third pass
acc *outfile
sort on employee_no
set sub name outfile2 KEEP AT EMPLOYEE_NO
rep sum employee_no &,
first_date MAX &,
second_date MAX
go
Does anyone know the technical term for this method ?
I call it Squeezing because it kinda squeezes two records together.
Seamus@partage.org
-----Message d'origine-----
De : powerh-l-request@cube.swau.edu
[mailto:powerh-l-request@cube.swau.edu]
Envoyé : jeudi 28 août 2003 13:25
À : powerh-l@lists.swau.edu
Objet : powerh-l digest, Vol 1 #763 - 7 msgs
Send powerh-l mailing list submissions to
powerh-l@lists.swau.edu
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.swau.edu/mailman/listinfo/powerh-l
or, via email, send a message with subject or body 'help' to
powerh-l-request@lists.swau.edu
You can reach the person managing the list at
powerh-l-admin@lists.swau.edu
When replying, please edit your Subject line so it is more specific than
"Re: Contents of powerh-l digest..."
Today's Topics:
1. Record count in Quiz (Mike Palandri)
2. RE:Record count in Quiz (Noack, Andreas M (GESM))
3. SV: Record count in Quiz
(=?iso-8859-1?Q?=22Selfors=2C_Bj=F8rn=22?=)
4. RE:Record count in Quiz (Edis, Robert)
5. RE:Record count in Quiz (Mike Palandri)
6. Re:Record count in Quiz (Markus Grossrieder)
7. Re:Record count in Quiz (Chris Sharman)
--__--__--
Message: 1
Date: Wed, 27 Aug 2003 07:41:57 -0700
From: Mike Palandri <palandri@4j.lane.edu>
Subject: Record count in Quiz
To: powerh-l@lists.swau.edu
Is there any way to get a hold of the record count in Quiz, so that its
value would be 1 when the first record was read, 2 when the second was
read, 3 when the third was read etc.?
I am trying to convert a report based on a QTP to Quiz, and the
technique
used requires the record count. This is done with temps in QTP, but I
can't seem to come up with it for Quiz:
TEMP TI-REC-CNT INT*10
[...]
ITEM TI-REC-CNT = TI-REC-CNT + 1
Thanks,
Mike
.
--__--__--
Message: 2
Subject: RE: Record count in Quiz
Date: Wed, 27 Aug 2003 16:01:43 +0100
From: "Noack, Andreas M (GESM)" <Andreas.Noack@gesm.ge.com>
To: <powerh-l@lists.swau.edu>
Cc: "Mike Palandri" <palandri@4j.lane.edu>
Hello Mike,
maybe I am too simplistic here, then correct...
I would create a numeric defined item with value 1 and at report
statement subtotal this. If indeed you need the count value per record
for some calculation then report to subfile and use it in a second
report.
Kind regards
g GE Water Technologies
-----------------------------------------
Andreas Noack
IT Team Europe
-----Original Message-----
From: Mike Palandri [mailto:palandri@4j.lane.edu]
Sent: Mittwoch, 27. August 2003 16:42
To: powerh-l@lists.swau.edu
Subject: Record count in Quiz
Is there any way to get a hold of the record count in Quiz, so that its
value would be 1 when the first record was read, 2 when the second was
read, 3 when the third was read etc.?
I am trying to convert a report based on a QTP to Quiz, and the
technique
used requires the record count. This is done with temps in QTP, but I
can't seem to come up with it for Quiz:
TEMP TI-REC-CNT INT*10
[...]
ITEM TI-REC-CNT = TI-REC-CNT + 1
Thanks,
Mike
.
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to
powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe <password>" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
--__--__--
Message: 3
Subject: SV: Record count in Quiz
Date: Wed, 27 Aug 2003 17:04:17 +0200
From: =?iso-8859-1?Q?=22Selfors=2C_Bj=F8rn=22?=
<Bjorn.Selfors@bergen.kommune.no>
To: "Mike Palandri" <palandri@4j.lane.edu>, <powerh-l@lists.swau.edu>
You can put it into a subfile like:
acc....
define cnt = 1
rep sum .... cnt subtotal noreset
set subfile name ...
mvh/regards
Bjørn Selfors, Delfi Data a.s.
-----Opprinnelig melding-----
Fra: Mike Palandri [mailto:palandri@4j.lane.edu]
Sendt: 27. august 2003 16:42
Til: powerh-l@lists.swau.edu
Emne: Record count in Quiz
Is there any way to get a hold of the record count in Quiz, so that its
value would be 1 when the first record was read, 2 when the second was
read, 3 when the third was read etc.?
I am trying to convert a report based on a QTP to Quiz, and the
technique
used requires the record count. This is done with temps in QTP, but I
can't seem to come up with it for Quiz:
TEMP TI-REC-CNT INT*10
[...]
ITEM TI-REC-CNT = TI-REC-CNT + 1
Thanks,
Mike
.
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to
powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe <password>" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
--__--__--
Message: 4
Subject: RE: Record count in Quiz
Date: Wed, 27 Aug 2003 10:16:35 -0500
From: "Edis, Robert" <REdis@Blistex.com>
To: "Powerh-L (E-mail)" <powerh-l@lists.swau.edu>
Doesn't Quiz have a summary operation called COUNT?
Blue
-----Original Message-----
From: Mike Palandri [mailto:palandri@4j.lane.edu]
Sent: Wednesday, August 27, 2003 9:42 AM
To: powerh-l@lists.swau.edu
Subject: Record count in Quiz
Is there any way to get a hold of the record count in Quiz, so that its
value would be 1 when the first record was read, 2 when the second was
read, 3 when the third was read etc.?
I am trying to convert a report based on a QTP to Quiz, and the
technique
used requires the record count. This is done with temps in QTP, but I
can't seem to come up with it for Quiz:
TEMP TI-REC-CNT INT*10
[...]
ITEM TI-REC-CNT = TI-REC-CNT + 1
Thanks,
Mike
.
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to
powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe <password>" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
--__--__--
Message: 5
Date: Wed, 27 Aug 2003 08:22:19 -0700
From: Mike Palandri <palandri@4j.lane.edu>
Subject: RE: Record count in Quiz
To: powerh-l@lists.swau.edu
Thanks for all the tips. I need the record count to sort on, so a
summary
operation such as count or subtotal won't help.
The problem is to read a record and save the value of one of the fields
so
it may be compared with another field in the next record. This is to
determine if the date range in the first record overlaps the range in
the
second. I've been sorting on the record count in QTP in order to
provide a
break on each record...
SORT ON EMP-NO, POS-NO, DATE-PLAN-START, TI-REC-CNT
...then saving an end date field in a temp when the record count breaks
(which it does on every record)...
ITEM TD-END-TMP = DATE-PLAN-END AT TI-REC-CNT RESET AT POS-NO
...so it can be compared with the start date in the next record:
DEF TC-DUP-FLAG CHAR*01 = "Y" &
IF TD-END-TMP <> 0 AND TD-END-TMP >= DATE-PLAN-START
&
ELSE ""
This tells me if the end date in record (n) is >= to the start date in
record (n + 1), if so the range in the second record overlaps the range
in
the first, and it is written to a subfile to be reported in Quiz
What I am trying to do is make this a simple one pass Quiz. I may as
well
keep the current QTP method if I am going to be forced to use two
passes
and a subfile in Quiz.
I can see as I think through what I am writing here, that I would also
have
an issue converting the ITEM TD-END-TMP... statement to Quiz as well.
Perhaps it's just not worth the trouble.
Thanks again.
At 10:03 AM 08/27/2003 -0500, you wrote:
>You can't use temp's in quiz and you cannot reference the count
>function
>directly. You can however use a defined variable to do the same thing.
>
>ACCESS CUSTOMERS
>
>DEFINE CTR NUM*4 = 1
>
>REPORT SUMMARY CUSTOMER-NUMBER CTR SUB NORESET
>
>GO
>
>This will increment the CTR variable for each record complex.
>
>
>
>-----Original Message-----
>From: Mike Palandri [mailto:palandri@4j.lane.edu]
>Sent: Wednesday, August 27, 2003 9:42 AM
>To: powerh-l@lists.swau.edu
>Subject: Record count in Quiz
>
>
>Is there any way to get a hold of the record count in Quiz, so that its
>value would be 1 when the first record was read, 2 when the second was
>read, 3 when the third was read etc.?
>
>
>I am trying to convert a report based on a QTP to Quiz, and the
>technique used requires the record count. This is done with temps in
>QTP, but I can't seem to come up with it for Quiz:
>
>TEMP TI-REC-CNT INT*10
>
>[...]
>
>ITEM TI-REC-CNT = TI-REC-CNT + 1
>
>
>Thanks,
>
>
>Mike
>.
>
>= = = = = = = = = = = = = = = = = = = = = = = = = = = = Mailing list:
>powerh-l@lists.swau.edu
>Subscribe: "subscribe" in message body to
>powerh-l-request@lists.swau.edu
>Unsubscribe: "unsubscribe <password>" in message body to
>powerh-l-request@lists.swau.edu
>http://lists.swau.edu/mailman/listinfo/powerh-l
>This list is closed, thus to post to the list you must be a subscriber.
Mike
.
--__--__--
Message: 6
From: "Markus Grossrieder" <markus.grossrieder@alba-systems.com>
To: "Mike Palandri" <palandri@4j.lane.edu>, <powerh-l@lists.swau.edu>
Subject: Re: Record count in Quiz
Date: Wed, 27 Aug 2003 17:25:54 +0200
Hi Mike,
DEFINE D_COUNT INT*8 = 1
...
REPORT ... D_COUNT SUBTOT
should work !
If you want to use it in footings, it will reset itself unless you use
NORESET resp. RESET AT ...
HTH, regards,
Markus
----- Original Message -----
From: "Mike Palandri" <palandri@4j.lane.edu>
To: <powerh-l@lists.swau.edu>
Sent: Wednesday, August 27, 2003 4:41 PM
Subject: Record count in Quiz
> Is there any way to get a hold of the record count in Quiz, so that
> its value would be 1 when the first record was read, 2 when the second
> was read, 3 when the third was read etc.?
>
>
> I am trying to convert a report based on a QTP to Quiz, and the
> technique used requires the record count. This is done with temps in
> QTP, but I can't seem to come up with it for Quiz:
>
> TEMP TI-REC-CNT INT*10
>
> [...]
>
> ITEM TI-REC-CNT = TI-REC-CNT + 1
>
>
> Thanks,
>
>
> Mike
> .
>
> = = = = = = = = = = = = = = = = = = = = = = = = = = = = Mailing list:
> powerh-l@lists.swau.edu
> Subscribe: "subscribe" in message body to
> powerh-l-request@lists.swau.edu
> Unsubscribe: "unsubscribe <password>" in message body to
powerh-l-request@lists.swau.edu
> http://lists.swau.edu/mailman/listinfo/powerh-l
> This list is closed, thus to post to the list you must be a
> subscriber.
>
>
--__--__--
Message: 7
Date: Wed, 27 Aug 2003 18:19:52 +0100
From: Chris Sharman <chris.sharman@ccagroup.co.uk>
To: Mike Palandri <palandri@4j.lane.edu>,
PowerH List
<powerh-l@lists.swau.edu>
Subject: Re: Record count in Quiz
Mike Palandri wrote:
> Is there any way to get a hold of the record count in Quiz, so that
> its
> value would be 1 when the first record was read, 2 when the second was
> read, 3 when the third was read etc.?
>
> I am trying to convert a report based on a QTP to Quiz, and the
> technique used requires the record count. This is done with temps in
> QTP, but I can't seem to come up with it for Quiz:
count works for reports of course, but not for subfiles, for which you
need something like:
def kount int*9 = 1
rep sum kount subt noreset
Chris
-----------------------------------------------------------------------
Any views expressed in this message are those of the sender and not
necessarily those of CCA Group. The unauthorized use, disclosure,
copying or alteration of this message is forbidden. The contents of
this message may be confidential and/or privileged, copyright CCA Group
and are intended solely for the use of the individual or entity to whom
they are addressed. Whilst this message has been scanned, CCA Group
cannot guarantee that it is virus free or compatible with your systems
and accepts no responsibility for any loss or damage arising from its
use. The recipient is advised to run their own anti-virus software. If
you receive this message in error please contact
postmaster@ccagroup.co.uk immediately, destroy any copies and delete it
from your computer systems.
--__--__--
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to
powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe <password>" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
End of powerh-l Digest
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe <password>" in message body to powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.