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

[pat_agen]

hi,

try this:

1. add to your first load the following field

     left(Field1,3) & '|' & Field2 as NewDateKey

2. add the following table

     newDate:

     load

           NewDateKey,

           min(Date2) as NewDate

     resident loadData

     group by NewDateKey;

3. join this table back to your initail load:

     originalData:
     join (originalData) load
           NewDateKey,
           NewDate
     resident newDate;

4. then drop the work table

     drop table newDate;

[swuehl]

Hi,

I think you could also use combined field3:

left(Field1,3) & Field2 as Field3

Then load a new table using your table sorted by Field3 and Date

newDateTable:

Load *,

if(peek(Field3)=Field3,peek(NewDate), NewDate) as NewDate

resident yourTable order by Field3, Date ASC;

Just an idea,

Stefan

[kaushiknsolanki]

HI

   Try this.

Data_Temp:

load * inline [

Date1,          Field11,          Field21

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

];

Data:

Load date(date#(Date1,'DD/MM/YYYY'),'DD/MM/YYYY') as Date,

Field11 as Field1,

left(Field11,3) as Field3,

Field21 as Field2

Resident Data_Temp;

Data1:

Load

Field3 ,

Field2 ,

Date(min(Date)) as Date2

Resident Data group by Field2,Field3;

Drop table Data_Temp;

Regards,

Kaushik Solanki

[#5]

No,didn't give me the wanted result

it compares NewDateKey field with itself but i want it to compare it with Field1

So,the NewDate wasn't right

[#6]

Not the expected result

it gives me each time the previous date

[lironbaram]

hei mona

attach is example of what you asked for

the example have contains two solutions:

1. in an expression look at the first expression

2. in the load script look inside the script

hope its helps you

[#8]

No,in your case it will give for each AAA-CM the minimum date for them all

but what i want is that

for example when getting the left(Field1,3) in case of AAAE which is AAA and have Field2=CM, i want to compare it with the first AAA which has the Field2=BB , so both of them shouldn't have the same NewDate which is the minimum one

No,Because of Field2 is different in both records each one will reserve its Date as it's

Is the problem clear??

[pat_agen]

I must have misunderstood.

I thought you wanted to test the first three characters of field 1 plus the value of field 2. This is what the NewDateKey contains.

for which record does it produce the wrong result, and what is the reulst you woudl expect to have - this may help me understand your reuirement better.

[#10]

Thanks for your help

but unfortunately not the wanted result

i want each time the( left(Field1,3)=Field3) to compare it self with Field1 again

so the only 2 records which will have the same date is the first and last one because

the first record has Field1=AAA and Field2=BB

record2 has Field3=AAA and Field2=CM and Field1=AAAA

record3 has Field3=AAA and Field2=CM and Field1=AAAB

when comparing record2 and 3 with record1 comparin Field3 in both with Field1 in record1 and comparing Field2 in the 3 records

record 2 not equal to record1 and the same record3 is not equal to record1

so each one will reserve each Date

I hope the problem is clear now

and Thanks