Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I didn't quite understand your three criterieas. Could you create a table with the expected output from your original data?
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;
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
See duplicate post: https://community.qlik.com/t5/QlikView-Scripting/quot-New-quot-status-according-to-year/m-p/1631755#...
Regards,
Brett