Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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
Highlighted
Contributor III
Contributor III

Re: Status according to conditions and year

any help please
Highlighted
Partner
Partner

Re: Status according to conditions and year

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
Highlighted
Contributor III
Contributor III

Re: Status according to conditions and year

 

this is the link of data file

https://filebin.net/0f3nx8h2nvpv9qtv

Highlighted
Creator
Creator

Re: Status according to conditions and year

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;

Highlighted
Digital Support
Digital Support

Re: Status according to conditions and year

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.
Highlighted
Digital Support
Digital Support

Re: Status according to conditions and year

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.