Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Measuring net valid contracts started vs ended- Help

I am having problems trying to create chart of net contracts started vs ended.  I have tried using insurance contract example for the link below and using the interval match and cant figure it out.  The loop creates millions of records.  I am trying to right the script so when I make one chart of total contracts shipped less expired year.

ChartDifference.PNG

Attached is the qvw with example data.

Generating Missing Data In QlikView

Tags (1)
1 Solution

Accepted Solutions

Re: Measuring net valid contracts started vs ended- Help

Hi,

one solution could be:

QlikCommunity_Thread_150305_Pic3.JPG

QlikCommunity_Thread_150305_Pic1.JPG

QlikCommunity_Thread_150305_Pic4.JPG

QlikCommunity_Thread_150305_Pic5.JPG

QlikCommunity_Thread_150305_Pic6.JPG

QlikCommunity_Thread_150305_Pic2.JPG

DistinctContracts:

LOAD *,

     Year(Date) as Shipyear,

     Date as Birthdate,

     Year(EndDate) as ExpYear,

     -(EndDate<Today()) as ContractEnded;   

LOAD ContractId as ContractIdShipped,

     Date(Min(StartDate)) as Date,

     Date(Max(EndDate)) as EndDate

Resident Contracts

Group By ContractId;

DROP Table Contracts;

MasterCalendar:

LOAD *,

     Day(CanDate) as Day,

     WeekDay(CanDate) as WeekDay,

     Week(CanDate) as Week,

     WeekName(CanDate) as WeekName,

     Month(CanDate) as Month,

     MonthName(CanDate) as MonthName,

     Dual('Q'&Ceil(Month(CanDate)/3),Ceil(Month(CanDate)/3)) as Quarter,

     QuarterName(CanDate) as QuarterName,

     Year(CanDate) as Year,

     WeekYear(CanDate) as WeekYear;

LOAD Date(MinDate+IterNo()-1) as CanDate

While MinDate+IterNo()-1<=MaxDate;

LOAD RangeMin(Min(Date),Min(EndDate)) as MinDate,

     RangeMax(Max(Date),Max(EndDate)) as MaxDate

Resident DistinctContracts;

tabLink:

CrossTable(DateType, CanDate)

LOAD ContractIdShipped,

     Date as Shipped,

     EndDate as Expired

Resident DistinctContracts;

hope this helps

regards

Marco

6 Replies
MVP
MVP

Re: Measuring net valid contracts started vs ended- Help

maybe this?

1.png

Re: Measuring net valid contracts started vs ended- Help

Hi,

one solution could be:

QlikCommunity_Thread_150305_Pic3.JPG

QlikCommunity_Thread_150305_Pic1.JPG

QlikCommunity_Thread_150305_Pic4.JPG

QlikCommunity_Thread_150305_Pic5.JPG

QlikCommunity_Thread_150305_Pic6.JPG

QlikCommunity_Thread_150305_Pic2.JPG

DistinctContracts:

LOAD *,

     Year(Date) as Shipyear,

     Date as Birthdate,

     Year(EndDate) as ExpYear,

     -(EndDate<Today()) as ContractEnded;   

LOAD ContractId as ContractIdShipped,

     Date(Min(StartDate)) as Date,

     Date(Max(EndDate)) as EndDate

Resident Contracts

Group By ContractId;

DROP Table Contracts;

MasterCalendar:

LOAD *,

     Day(CanDate) as Day,

     WeekDay(CanDate) as WeekDay,

     Week(CanDate) as Week,

     WeekName(CanDate) as WeekName,

     Month(CanDate) as Month,

     MonthName(CanDate) as MonthName,

     Dual('Q'&Ceil(Month(CanDate)/3),Ceil(Month(CanDate)/3)) as Quarter,

     QuarterName(CanDate) as QuarterName,

     Year(CanDate) as Year,

     WeekYear(CanDate) as WeekYear;

LOAD Date(MinDate+IterNo()-1) as CanDate

While MinDate+IterNo()-1<=MaxDate;

LOAD RangeMin(Min(Date),Min(EndDate)) as MinDate,

     RangeMax(Max(Date),Max(EndDate)) as MaxDate

Resident DistinctContracts;

tabLink:

CrossTable(DateType, CanDate)

LOAD ContractIdShipped,

     Date as Shipped,

     EndDate as Expired

Resident DistinctContracts;

hope this helps

regards

Marco

Not applicable

Re: Measuring net valid contracts started vs ended- Help

This is great.  Thanks for the help.  I haven't used CrossTable's so far and I see how useful it can be.  Your solution is more akin to my original link above.


Marco's solution also works using a  date island:

Island:

load

  Distinct ExpYear as Year

Resident

  DistinctContracts;

Concatenate (Island)

load

  Distinct Shipyear as Year

Resident

  DistinctContracts

Where

  not Exists (Year, Shipyear);

Not applicable

Re: Measuring net valid contracts started vs ended- Help

Thanks I meant Massimo in my orginal reply.  Much thanks....

Re: Measuring net valid contracts started vs ended- Help

You're welcome.

Please mark one of the answers correct that answered your question.

thanks

regards

Marco

Not applicable

Re: Measuring net valid contracts started vs ended- Help

will do, thanks.

Community Browser