what's the expression

[#1]

Hi,

i have this table and i need an expression to evaluate the NewDate

Date	Field1	Field2	NewDate
17/4/2011	AAA	BB	?
18/4/2011	AAAA	CM	?
19/4/2011	AAAB	CM	?
20/4/2011	AAAC	CM	?
21/4/2011	AAAD	CM	?
22/4/2011	AAAE	RH	?
23/4/2011	AAAF	CM	?
24/4/2011	AAAG	BB	?

in the load i added load for left(Field1,3) and called it Field3

what i want is that"if Field3=Field1 and Field2 is equal in both records"

for example in case of Field1=AAAG and the left(Field,3) will equal AAA the corresponding value for Field2 in this record =BB

and there's AAA in Field1 and its corresponding value in Field2 is BB

So, i want the NewDate for both records to be the minimum date of both records which is 17/4/2011

The final result i want is

Date	Field1	Field2	NewDate
17/4/2011	AAA	BB	17/4/2011
18/4/2011	AAAA	CM	18/4/2011
19/4/201	AAAB	CM	19/4/2011
20/4/2011	AAAC	CM	20/4/2011
21/4/2011	AAAD	CM	21/4/2011
22/4/2011	AAAE	RH	22/4/2011
23/4/2011	AAAF	CM	23/4/2011
24/4/2011	AAAG	BB	17/4/2011

Thanks in advance

[#2]

Never Mind,

The expected result is the second table in my original post

the only 2 records will be affected are record one and three

[lironbaram · Accepted solution]

hei mona

check the new attachment

last try

look at the load script

[#4]

Thanks Alot