How to retain a variable

[Niki Thorne-2]

Hiya,

  I am working with transactional data where one transaction can have many journey's and a journey is made up of of may leg's.

  To start with I have 2 tables one which gives me transactions ID, journey ID and journey start datetime.  On a second table I have transaction ID, leg ID and leg start datetime.

  I have been able to join these two tables by transaction ID, leg ID and start datetime but this only gives me a  journey ID for the first leg of each journey.

  How do I go about retaining the journey id for all legs within the journey?

  Thanks.

  Niki


---------------------------------
 Yahoo! Answers - Got a question? Someone out there knows the answer. Tryit now.

[Richard Ristow]

At 09:30 AM 4/17/2007, Niki Thorne wrote:

>   I am working with transactional data where one transaction can have
> many journey's and a journey is made up of of may leg's.
>
>   To start with I have 2 tables one which gives me transactions ID,
> journey ID and journey start datetime.  On a second table I have
> transaction ID, leg ID and leg start datetime.
>
>   I have been able to join these two tables by transaction ID, leg ID
> and start datetime but this only gives me a  journey ID for the first
> leg of each journey.

You're probably doing this with MATCH FILES. For the first table, on
record per journey, replace /FILE= by /TABLE=. Remember: for the first
one, the record per journey, not the second, with multiple records per
journey.

What I'm not sure I understand, so I may be missing something, is how
you got as far as you did - I'd have expected that your MATCH FILE
failed. Can you post the MATCH FILE syntax? Particularly, what are the
variable on the /BY list?

[Niki Thorne-2]

Hiya,

  Thanks for coming back to me - sorry just reading your reply realised that I forgot on dataset 1 this is transaction ID, journey ID , leg ID and outdatetime for the first leg only and dataset 2 is all leg's within a transaction with the outdatetime by no journey link.

  Here is an example of the matched data

  transid       journey id            legid           outdatetime
  1               1                            1              23/3/2004 5:50:00
  1                                             2              23/3/2004 20:10:00
  1               2                            7              15/3/2004  10:50:00
  1                                            8               15/3/2004   12:10:10

  and here is my syntax

  DATASET ACTIVATE DataSet9.
  MATCH FILES /FILE=*
  /TABLE='DataSet2'
  /BY TransactionId LegId out_date_time.
  EXECUTE.

  I am wondiner if I can somehow create a loop within transaction where you keep the journey id for the first leg then compare the second leg to see if the ID is in sequential order and if so then also output journey ID until the journey ID is not in sequential order eg an increment of one greater ?

  Thanks

  Niki

Richard Ristow <[hidden email]> wrote:
  At 09:30 AM 4/17/2007, Niki Thorne wrote:

> I am working with transactional data where one transaction can have
> many journey's and a journey is made up of of may leg's.
>
> To start with I have 2 tables one which gives me transactions ID,
> journey ID and journey start datetime. On a second table I have
> transaction ID, leg ID and leg start datetime.
>
> I have been able to join these two tables by transaction ID, leg ID
> and start datetime but this only gives me a journey ID for the first
> leg of each journey.

You're probably doing this with MATCH FILES. For the first table, on
record per journey, replace /FILE= by /TABLE=. Remember: for the first
one, the record per journey, not the second, with multiple records per
journey.

What I'm not sure I understand, so I may be missing something, is how
you got as far as you did - I'd have expected that your MATCH FILE
failed. Can you post the MATCH FILE syntax? Particularly, what are the
variable on the /BY list?




---------------------------------
 Yahoo! Mail is the world's favourite email. Don't settle for less, sign up for your freeaccount today.

[Richard Ristow]

At 10:19 AM 4/17/2007, Niki Thorne wrote:

>On dataset 1 this is transaction ID, journey ID , leg ID and
>outdatetime for the first leg only and dataset 2 is all leg's within a
>transaction with the outdatetime by no journey link.
>
>   Here is an example of the matched data

Alas, the dreaded long data lines! (General note: avoid posting lines
longer than 72 characters; the list remailer will wrap them at 72, and
they can become very difficult to read.) Below, reformatted:

>   transid     journey id     legid     outdatetime
>   1               1            1      23/3/2004  5:50:00
>   1                            2      23/3/2004 20:10:00
>   1               2            7      15/3/2004 10:50:00
>   1                            8      15/3/2004 12:10:10
>
>and here is my syntax
>
>   DATASET ACTIVATE DataSet9.
>   MATCH FILES /FILE=*
>   /TABLE='DataSet2'
>   /BY TransactionId LegId out_date_time.
>   EXECUTE.

What are the variables in Dataset9? and in Dataset2? It looks like
'legid' 'outdatetime' (did you misspell that in your data, or your
code?) occur only in Dataset9 (though if so, I wonder why the MATCH
FILES worked at all). Does something like this do it?

   DATASET ACTIVATE DataSet9.
   MATCH FILES /FILE=*
   /TABLE='DataSet2'
   /BY TransactionId journeyid

I've added 'journeyid' to the /BY list, and dropped 'LegId' and
'out_date_time'.