Status according to conditions and year

[capriconuser]

Hello 

I have this data.. i want to put status based on different conditions 

data

Parent Policy	From Date	To Date	UW_YEAR	status
MMI/8544	1-Jun-2017	1-Jun-2018	2018
MMI/85429	1-Jun-2016	3-Dec-2016	2018
MMI/854434	1-Jun-2019	31-Dec-2019	2018
MMI/859	4-Jul-18	31-May-19	2018
MMI/8239	24-Dec-18	31-May-19	2018
MMI/6749	2-Mar-19	31-May-19	2018
MMI/0989	2-Mar-19	1-Mar-21	2019

Conditon 

1) if policy did not complete one year and came first time in records then this is consider to be as "New"  and year should be current year 

2) If policy did not complete one year and same policy available in previous year then consider to be as "Renew"

3) Policy which is available to previous years but not available in current year then considered to be as "LOST"

The year is come from "from date " and "to date"

how i do this in script 

this is what i load in sscript

Load 
POLICY_NO as [Child Policy],
OLD_POLICY_NUMBER as [Parent Policy],
UW_YEAR,
Date(POLICY_EFFECTIVE_DATE) as [From Date],
Date(POLICY_EXPIRY_DATE) as [To Date]
FROM[D:\test\table_1.qvd]
(qvd);

now i want to add here "Status" according to above mentioned 3 conditions 

[capriconuser]

any help please

[Vegar]

I didn't quite understand your three criterieas. Could you create a table with the expected output from your original data?

[capriconuser]

this is the link of data file

https://filebin.net/0f3nx8h2nvpv9qtv

[gardenierbi]

Try this :

tempData:
LOAD
    [Child Policy],
    [Parent Policy],
    [From Date],
    [To Date],
    UW_YEAR
FROM [Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

PolicyStatus:
NOCONCATENATE
LOAD
    *,
    IF([Child Policy] = PREVIOUS([Child Policy]),
      IF((UW_YEAR - 1) <= PREVIOUS(UW_YEAR),
        'Renew',
        'Lost'),
      'New') AS 'Policy status'
RESIDENT tempData
ORDER BY
    [Child Policy],
    UW_YEAR,
    [From Date];

DROP TABLE tempData;

[Brett_Bleess]

Did Sander's post meet your requirement?  If so, do not forget to come back and use the Accept as Solution button to let others know that worked and to give him credit for the help.  If you did something different, consider posting that and marking it, and if you are still working on things, leave an update on where you stand.

Regards,
Brett

[Brett_Bleess]

See duplicate post: https://community.qlik.com/t5/QlikView-Scripting/quot-New-quot-status-according-to-year/m-p/1631755#...

Regards,

Brett