topic Mark latest record on the basis date as considered else not considered in App Development

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

P_Kale — Thu, 04 Jan 2024 11:15:33 GMT

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

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

MATC — Thu, 04 Jan 2024 11:33:39 GMT

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')

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

P_Kale — Thu, 04 Jan 2024 11:36:52 GMT

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

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

MATC — Thu, 04 Jan 2024 11:43:09 GMT

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.

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

anat — Thu, 04 Jan 2024 11:49:05 GMT

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..

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

Parthiban — Thu, 04 Jan 2024 12:04:18 GMT

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.

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

P_Kale — Thu, 04 Jan 2024 19:24:30 GMT

Thanks It works.