Mark latest record on the basis date as considered else not considered

[P_Kale]

Hi,

I am having 1 scenario where i have to mark in data latest record on the basis of Interaction_Created_Date for same policy as "Considered" and all other records of same policy no. as "Not_considered" below given is the e.g

CHDRNUM	INTERACTION_CREATED_DATE	Remark
456789	20/07/2023	Not_considered
456789	25/08/2023	Not_considered
456789	04/12/2023	Considered

& if only single record is there then mark it as "Considered".

Can anyone help to achieve this

Thanks in advance

[MATC]

You could create a variable with max value of your date field. Then based on that variable create if statement if(variable=INTERACTION_CREATED_DATE, 'Considered','Not_considered')

[P_Kale]

Sorry but not understood. How to create a max date variable. As every policy will have different dates.

[MATC · Accepted solution]

Didn't understand that you had different policies and you wanted to have a max date on a number of policies.
So instead of variable you can create a field in script by creating new temp table.

temp:
load 
CHDRNUM
max(INTERACTION_CREATED_DATE) as field
resident source_table
group by CHDRNUM;

And then create another table with given if statement but instead of variable you can use this new field to create the remarks field.

[anat]

give a try something like below:

test:

load id,date,id&date as Key from source

left join(test)

load id,id&maxdate as Key1

load id,max(date) as maxdate resident test group by id;

noconcatenate

load id,date,maxdate,if(Key=Key1,'considered','Not condidered') as flag resident test;

drop table test;

Note:this is not the exact script,need to do some changes as per your date format..

[Parthiban]

Hi,

Try this 

First create a variable like,

MaxDate = Max(INTERACTION_CREATED_DATE)

Then,

=If(Num(INTERACTION_CREATED_DATE)=$(MaxDate),'Considered','Not_Considered')

You will get answer.

[P_Kale]

Thanks It works.