Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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')
Sorry but not understood. How to create a max date variable. As every policy will have different dates.
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.
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..
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.
Thanks It works.