Derive New Field

[alvinford]

Hi All,

I have the following table sorted by DateAndTime field and can be grouped by IDLOC column.

IDLOC	DateAndTime	UpdatedDateandTime
P01	20131120080000
P01	20131120083000	20131120150000
P01	20131121090000
P03	20131201100000
P03	20131201140000	20131202030000
P03	20131201190000

My requirement is I need to compare the UpdatedDateandTime with DateAndTime i.e. UpdatedDateandTime has to be  compared with

Previous(DateAndTime), DateAndTime and Next(DateAndTime)  for the same group IDLOC. Based on the comparision need to derive New Field indicating the Flag.

If the UpdatedDateandTime is Greater Than DateAndTime then 1 else 0. After deriving the table will be as below.

IDLOC	DateAndTime	UpdatedDateandTime	NewField
P01	20131120080000
P01	20131120083000	20131120150000	1
P01	20131121090000
P03	20131201100000
P03	20131201140000	20131202030000	0
P03	20131201190000

Thanks in Advance..

Regards,

Alvin.

[#2]

Please find the attached application:

DATA:

load IDLOC, Num(Timestamp#(DATE_TIME,'YYYYMMDDhhmm')) AS DATE_TIME, Num(Timestamp#(UPDATE_TIME,'YYYYMMDDhhmm')) AS UPDATE_TIME

inline [

IDLOC,DATE_TIME, UPDATE_TIME

P01,201311200800,

P01,201311200830,201311201500

P01,201311210900,

P02,201312011000,

P02,201312011400,201312020300

P02,201312011900,

];

Left Join (DATA)

LOAD IDLOC, IF(Max(UPDATE_TIME) > Max(DATE_TIME) ,0,1) AS FLAG

Resident DATA Group by IDLOC;

[alvinford]

Hi Ciro ,

Once again thanks for your valuable reply. I think I have to modify my query i.e. I have the following records

RowNo	IDLOC	DateAndTime	UpdatedDateandTime
1	P01	20131120080000
2	P01	20131120083000	20131120150000
3	P01	20131121090000
4	P01	20131121120000
5	P03	20131201100000
6	P03	20131201140000	20131202030000
7	P03	20131201190000
8	PO3	20131201230000

for the IDLOC PO1 i have to compare the RowNo 2 with 1 and 3 and maintain a flag at RowNo 2 and same for the IDLOC PO3 I need to compare RowNo 6 with 5 and 7 and maintain the Flag in 6. Which will be as below

RowNo	IDLOC	DateAndTime	UpdatedDateandTime	NewField
1	P01	20131120080000
2	P01	20131120083000	20131120150000	1
3	P01	20131121090000
4	P01	20131121120000
5	P03	20131201100000
6	P03	20131201140000	20131202030000	0
7	P03	20131201190000
8	PO3	20131201230000

Regards,

Alvin.

[#4]

Hi, with my script you compare each line for each IDLOC with the same value of DAtaAndTime (replicate on each line for the join) , the result is a NewField with value for each line, if you want to have a single value on the same line of UpdateDateandTime , i think is necessary another load .....resident.... with a condition i.e.:IF (LEN(UpdatDAteandTime)>1,NewField) and you obtain value for NewField only on the row where ther is the value of UpdateDateandTIme.
Regards