Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
capriconuser
Creator
Creator

Status according to conditions and year

Hello 

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

data

Parent PolicyFrom DateTo DateUW_YEARstatus
     
MMI/85441-Jun-20171-Jun-20182018 
MMI/854291-Jun-20163-Dec-20162018 
MMI/8544341-Jun-201931-Dec-20192018 
MMI/8594-Jul-1831-May-192018 
MMI/823924-Dec-1831-May-192018 
MMI/67492-Mar-1931-May-192018 
MMI/09892-Mar-191-Mar-212019 

 

 

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 

Labels (3)
6 Replies
capriconuser
Creator
Creator
Author

any help please
Vegar
MVP
MVP

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
capriconuser
Creator
Creator
Author

 

this is the link of data file

https://filebin.net/0f3nx8h2nvpv9qtv

gardenierbi
Creator
Creator

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Brett_Bleess
Support (Former)
Support (Former)

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

Regards,

Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.