how to check two fields in two different records (recursion) ??

[#1]

hi

i have a problem and want your help ,please

i have this data

Main  header	Custom header	description  Id	date
10-19168	10-19168-A-A-A-A-C	CM	3/12/2010 07:37:18
10-19168	10-19168-A-B	HP	1/10/20110 06:49:47
10-19168	10-19168-A-A-A-B-B	CM	23/11/2010 03:05:16
10-19168	10-19168-A-A-A-A-B-A	CM	5/12/2010 02:58:00
10-19168	10-19168-A-A-A-A-E	RC	3/12/2010 07:37:18
10-19168	10-19168-A-A-A-A-A-A	CM	5/12/2010 02:58:18
10-19168	10-19168-A-A-A	BM	3/10/2010 12:17:49
10-19168	10-19168-A-A-A-B	CM	12/10/2010 04:54:13
10-19168	10-19168-A-A-A-B-C	CM	23/11/2010 03:05:16
10-19168	10-19168-A-A-A-B-E	CM	23/11/2010 03:05:16
10-19168	10-19168-A-A-A-A-F	SC	3/12/2010 07:37:18
10-19168	10-19168-A-A-A-B-A	CM	23/11/2010 03:05:16
10-19168	10-19168-A-A	FB	1/10/2010 06:49:16
10-19168	10-19168-A-A-A-A-B	CM	3/12/2010 07:37:18
10-19168	10-19168-A-A-A-A-A	CM	3/12/2010 07:37:18
10-19168	10-19168-A-A-A-B-D	CM	23/11/2010 03:05:16
10-19168	10-19168-A	HP	30/9/2010 08:17:43
10-19168	10-19168-A-A-A-B-E-A	CM	10/1/2011 02:55:31
10-19168	10-19168-A-A-A-C	RC	12/10/2010 04:55:15
10-19168	10-19168-A-A-A-A-D	CM	3/12/2010 07:37:18
10-19168	10-19168-A-A-A-A	CM	12/10/2010 04:53:40

which explain one case of the data

the structure of the data is => each main header has many custom header  => for each

custom header has description id "description id is not unique " and date

my problem is to find the real date for each custom header by using this algorithm

1-for each custom header has the same main header i'll check the description id of the current custom header and one step back custom header not the previous to be clear for example

               1- current custom header => 10-19168-A-A-A-B , the one step back is => 10-19168-A-A-A

               2- current custom header => 10-19168-A-A-A-B-E-A , the one step back is => 10-19168-A-A-A-B-E

                        " one step back mean removing last '-' and the letter after it "

then if the description id

               1- diffrent => then the real date of the current custom header  will be  the date of current custom header

               2- same => then the real date of the current custom header will be  the real date

of  the one step back custom header "which has been found with the same algorithm   "

so the final result will be the following table

Main  header	Custom header	description  Id	date	Real  Date
10-19168	10-19168-A-A-A-A-C	CM	3/12/2010 07:37:18	12/10/2010 04:53:40
10-19168	10-19168-A-B	HP	1/10/20110 06:49:47	30/9/2010 08:17:43
10-19168	10-19168-A-A-A-B-B	CM	23/11/2010 03:05:16	12/10/2010 04:54:13
10-19168	10-19168-A-A-A-A-B-A	CM	5/12/2010 02:58:00	12/10/2010 04:53:40
10-19168	10-19168-A-A-A-A-E	RC	3/12/2010 07:37:18	3/12/2010 07:37:18
10-19168	10-19168-A-A-A-A-A-A	CM	5/12/2010 02:58:18	12/10/2010 04:53:40
10-19168	10-19168-A-A-A	BM	3/10/2010 12:17:49	3/10/2010 12:17:49
10-19168	10-19168-A-A-A-B	CM	12/10/2010 04:54:13	12/10/2010 04:54:13
10-19168	10-19168-A-A-A-B-C	CM	23/11/2010 03:05:16	12/10/2010 04:54:13
10-19168	10-19168-A-A-A-B-E	CM	23/11/2010 03:05:16	12/10/2010 04:54:13
10-19168	10-19168-A-A-A-A-F	SC	3/12/2010 07:37:18	3/12/2010 07:37:18
10-19168	10-19168-A-A-A-B-A	CM	23/11/2010 03:05:16	12/10/2010 04:54:13
10-19168	10-19168-A-A	FB	1/10/2010 06:49:16	1/10/2010 06:49:16
10-19168	10-19168-A-A-A-A-B	CM	3/12/2010 07:37:18	12/10/2010 04:53:40
10-19168	10-19168-A-A-A-A-A	CM	3/12/2010 07:37:18	12/10/2010 04:53:40
10-19168	10-19168-A-A-A-B-D	CM	23/11/2010 03:05:16	12/10/2010 04:54:13
10-19168	10-19168-A	HP	30/9/2010 08:17:43	30/9/2010 08:17:43
10-19168	10-19168-A-A-A-B-E-A	CM	10/1/2011 02:55:31	12/140/2010 04:54:13
10-19168	10-19168-A-A-A-C	RC	12/10/2010 04:55:15	12/10/2010 04:55:15
10-19168	10-19168-A-A-A-A-D	CM	3/12/2010 07:37:18	12/10/2010 04:53:40
10-19168	10-19168-A-A-A-A	CM	12/10/2010 04:53:40	12/10/2010 04:53:40

[lironbaram]

hei

attach is an example of what you asked for

the logic in the script is

creating the previous step key by

concatanting the custom header after removing the last two chars to the description id

then join to theis table the original table

where the key will be the original custom header and desc id

hope its helps you

[#3]

thanks for you help but the result is not exactly what i want

in the attched photo i pointed to the records which have the problem

to be clear in this record i need the real date is to be tha same as the real date of the one step back "Not the date " so

10-19168   10-19168-A-A-A-A-A-A    CM   05/12/2010 02:58    03/12/2010 07:37      should be

10-19168   10-19168-A-A-A-A-A-A    CM   05/12/2010 02:58    12/10/2010 04:53

10-19168   10-19168-A-A-A-B-E-A   CM   10/01/2011 02:55   23/11/2010 03:05    should be

10-19168   10-19168-A-A-A-B-E-A   CM   10/01/2011 02:55   12/10/2010 04:54

[#4]

any solution please??

i have the same problem also!

[lironbaram]

hei

last try ,

i change the algoritm a little bit

what the script does is creating two keys :

the current key, prev stage key,sorting the table

then it finds the index of the record of the previous key,

and peeking the date if Previous Stage is Exists

on the example Data it works

now you shuld test it on large databut it should work

hope its helps you

[#6]

Hi,

i'm trying your last solution on my DB

but after finishing loading the script it gives me the following message

"Execution of script fgailed.Reload old data?"

i don't know what's the problem and how to solve such an issue

Note:

before your script i made a load for only 2 tables and there's joining between them

[lironbaram]

hei

can you upload

a sample file or just your script

i'll try it on sample data

[#8]

Hi,

Here is the script

MoveTicketHistory:

LOAD resource_no,

     left(resource_no,2) as ResourceNo,

     move_ticket,

    date_created,

    cust_move_ticket,

     left(cust_move_ticket,len(cust_move_ticket)-2) as StepBack,

     Mainmoveticket

FROM

(qvd);

Inner Join

load Timestamp( MinString(date_created)) as [Creation Date],

cust_move_ticket

Resident MoveTicketHistory

Group by cust_move_ticket;

DataTemp:

load *,

  ResourceNo & cust_move_ticket   as Key,

  ResourceNo & StepBack  as PrevKey

Resident MoveTicketHistory

order by ResourceNo,cust_move_ticket;

drop table MoveTicketHistory;

Data:

LOAd *,

  FieldIndex('Key',PrevKey) as index,

if(FieldIndex('Key',PrevKey)=0,[Creation Date],Peek('NewDate',FieldIndex('Key',PrevKey)-1)) as NewDate

resident DataTemp;

drop Table DataTemp;

Thanks in advance for your help

[#9]

Hi,

First Create a field with right trimming the last 2 characters of Custom Header Field and name it Main Header field.

Then load a new resident table like

MainHeaderTable:

load Main Header,date resident "HeaderTable"

then join this new MainHeaderTable with HeaderTable

that's all i think

good luck.