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.

[sivarajs]

if(num(DateAndTime)>num(UpdatedDateandTime),0,1)

[#3]

First convert your date fields into Timestamps and then compare. Please try like below:

IF(NUM(TIMESTAMP#(DateAndTime,'YYYYMMDDhhmmss')) > NUM(TIMESTAMP#(UpdatedDateandTime,'YYYYMMDDhhmmss')) , 0 ,1) AS NewField

[alvinford]

Hi Dathu,

Thanks for your reply.

I want to compare with the Previous and Next  and Same row , if I do as suggested by you , it will only compare the same row. Which is not my requirement.

Regards,

Alvin.

[#5]

You can to use the functions "below" and "above" for to valuate the preceeding and following row.

Regards

Ciro

[saumyashah90]

You can use previous function in Script

if(Num(TIMESTAMP#(UpdatedDateandTime,'YYYYMMDDhhmmss'))=Previous(NUM(TIMESTAMP#(DateAndTime,'YYYYMMDDhhmmss'))),1,0) as NewField

[alvinford]

Hi Saumya

Thanks for your reply.

I want to compare with the Previous and Next  and Same row , if I do as suggested by you , it will only compare the same row. Which is not my requirement.

Regards,

Alvin.

[#8]

Please, see the attachment
I hope reply your problem with solution...

Regards

[alvinford]

Hi Ciro,

Thank you for your valuable reply. The solution which you have provided is correct, but i want to perform at the script level as I want to use the field for other calculation.

Regards,

Alvin.

[#10]

Well, see now the attachment...