topic Re: ADD MONTHS in QlikView

ADD MONTHS

upaliwije — Wed, 08 Aug 2018 08:57:09 GMT

Hi Friends

PERIOD_FROM	PERIOD_TO
22-03-17	21-01-18
07-04-17	06-04-18
07-04-17	06-04-18
03-08-17	02-08-18
08-08-17	07-08-18
31-08-17	30-08-18
15-09-17	14-09-18
31-08-17	30-08-18
19-09-17	18-09-18

I have above date range. I want to add all month end dates between two dates. Pls help me

Eg 22-03-17 21-01-18

add months 31-03-17,30-04-17, 31-05-17 ................31-01-18

Re: ADD MONTHS

kfoudhaily — Wed, 08 Aug 2018 09:40:01 GMT

hello,

I have used interval match with a calender merged with your intervals data in order to get all mon starts from the calender:

cout of month starts of each of your intervals:

when selecting one date range:

please check script in qvw!

Best Regards,

---------------------------------------------

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Re: ADD MONTHS

upaliwije — Wed, 08 Aug 2018 09:52:16 GMT

Thanks Dear

I will chcek abd come back to you,. If ypu can help me my previous thread "

Distribution of Days among months'

I will be much greatful to you

Re: ADD MONTHS

kfoudhaily — Wed, 08 Aug 2018 10:05:31 GMT

I see, not simple problem but I think you will have to use same method in qvw above (intervalmatch fonction)

not very available during the day, I will have a look at your problem tonight.

regards,

Re: ADD MONTHS

upaliwije — Wed, 08 Aug 2018 10:51:19 GMT

Ok Thanks Dear

Re: ADD MONTHS

Anil_Babu_Samineni — Thu, 09 Aug 2018 00:03:37 GMT

This also works as your start date is Mar-2017 and Max date is Sep-2018. Below script generate all month end till date from source.

Period_Table:

LOAD * Inline [

PERIOD_FROM, PERIOD_TO

22-03-17, 21-01-18

07-04-17, 06-04-18

03-08-17, 02-08-18

08-08-17, 07-08-18

31-08-17, 30-08-18

15-09-17, 14-09-18

31-08-17, 30-08-18

19-09-17, 18-09-18

];

CalendarMaster:

LOAD Date(DateField) AS PERIOD_FROM, MonthEnd(DateField) as MonthEnd;

LOAD Date(MinDate + IterNo() - 1) as DateField While (MinDate + IterNo() - 1) <= Num(MaxDate);

LOAD Min(PERIOD_FROM)as MinDate,

Max(PERIOD_TO) as MaxDate

Resident Period_Table;

Re: ADD MONTHS

upaliwije — Thu, 09 Aug 2018 06:30:06 GMT

Thanks Anil

Your method meets my requirement . But I want to add something to it . I have some other fields in my data table. Eg Policy No . When I apply your script to my table I get only one month end date for each policy whereas I want all Month end dates between Period_from to Period_to against each policy.

Example shown below. I have also attached my sample data. Kindly help me please

POLICY_NO	TRN_DATE	PERIOD_FROM	PERIOD_TO	INTERVAL	MonthEnd
100	7/7/2017	3/22/2017	1/21/2018	306	3/31/2017
100	7/7/2017	3/22/2017	1/21/2018	306	4/30/2017
100	7/7/2017	3/22/2017	1/21/2018	306	5/31/2017
100	7/7/2017	3/22/2017	1/21/2018	306	6/30/2017
100	7/7/2017	3/22/2017	1/21/2018	306	7/31/2017
100	7/7/2017	3/22/2017	1/21/2018	306	8/31/2017
100	7/7/2017	3/22/2017	1/21/2018	306	9/30/2017
100	7/7/2017	3/22/2017	1/21/2018	306	10/31/2017
100	7/7/2017	3/22/2017	1/21/2018	306	11/30/2017
100	7/7/2017	3/22/2017	1/21/2018	306	12/31/2017
100	7/7/2017	3/22/2017	1/21/2018	306	1/31/2018