Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

markramiro
Contributor II

Load Script - break apart a schedule into individual months

I am trying to replicate a chart expression onto the Load Script so that the generated field, PeriodCost, can be utilized further.

I have imported data like such:

Property KeyStart DateEnd DateFrequencyCost
A0014/1/20189/30/2018Quarterly200
B0011/15/20181/14/2019Annually1000
C0011/15/20181/14/2019Quarterly120
D0011/1/20186/30/2018Monthly500

It represents sort of a payment schedule.  For example, for A001, it has a payment of 200 on a quarterly basis (every 3 months) starting 4/1/2018 (until 9/30/2019) i.e. 6 quarterly payments consisting of 4/1/2018, 7/1/2018, 10/1/2018, 1/1/2019, 4/1/2019, 7/1/2019. For D001, it has a payment of 500 every month starting 1/1/2018 (until 6/30/2018) i.e. 6 monthly payments consisting of 1/1/2018, 2/1/2018, 3/1/2018, 4/1/2018, 5/1/2018, 6/1/2018.

Using the Load Script, I am trying to create a table where the payments schedule is broken down by month (e.g. 2018-Jan, 2018-Feb, etc.) showing the specific instance of each payment. With some help from this community a while back, I am able to do this as an expression in a chart on my dashboard but this time I would like to do this within the Load Script so that this generated table can be used further (not just in the chart).

The table I am able to create (see "OUTPUT" on the attached) was initially suitable except that it does not factor the [Frequency] field. You will see on the attached QVW file on the table called "OUTPUT" that every cost is repeated every month from Start Date to End Date regardless of [Frequency]. I am seeking to modify the script so that Frequency is factored in. For example, for A001, since the schedule refers to a  “Quarterly” (i.e. once every 3 months) frequency, it should show on the created table as:

Property KeyYear-MonthPeriodCost
A0012018-Apr200
A0012018-Jul200
A0012018-Oct200
A0012019-Jan200
A0012019-Apr200
A0012019-Jul200

Currently the resulting table is showing a value of 200 for every month from 4/1/2018 to 9/30/ 2019, which should not be the case as I need it to reflect the actual instances based on the Frequency.

For B001, the Frequency is “Annually” (i.e. once every 12 months) hence it should show on the resulting table as :

Property KeyYear-MonthPeriodCost
B0012018-Jan1000

Currently the resulting table is showing a value of 1000 for every month from 1/15/2018 to 1/14/2019, which should not be the case as I need it to reflect the actual instances based on the Frequency.

1 Solution

Accepted Solutions
Siva_Sankar
Honored Contributor

Re: Load Script - break apart a schedule into individual months

Hi Mark Ramiro,

Solution attached

View solution in original post

5 Replies
Siva_Sankar
Honored Contributor

Re: Load Script - break apart a schedule into individual months

Hi Mark Ramiro,

Solution attached

View solution in original post

markramiro
Contributor II

Re: Load Script - break apart a schedule into individual months

Thank you. This is really really helpful. I encountered one little bit. I noticed that if I happen to have in my data a record that has dates which are in the middle of the year, the output is not pulling the desired dates when Frequency is "Annually".

For example, please see for PropertyKey=E001 which I added on the revised qvw file. It has a Start Date of 3/15/2018 (March 15, 2018) and an End Date of 3/14/2021 (March 14, 2021) i.e. it would consist of 3 payments, 2018-Mar, 2019-Mar, and 2020-Mar.

In the current output it starts on January of the following year.

Property KeyDateKeyPeriodCostYearMonth
E0011/1/20198882019-Jan
E0011/1/20208882020-Jan
E0011/1/20218882021-Jan

But it should show as March starting with the year of the start date.

Property KeyDateKeyPeriodCostYearMonth
E0013/1/20188882018-Mar
E0013/1/20198882019-Mar
E0013/1/20208882020-Mar

So essentially the Month of the Start Date for a row with "Annually" Frequency should be the month start, whether it is January or March or any other month.

I've been trying to leverage the first_month_of_year portion of the Yearstart function in the Where clause but I'm not getting the desired results.

markramiro
Contributor II

Re: Load Script - break apart a schedule into individual months

Think I got it. I split the WHERE for "Annually" into two clauses (one for those starting in January and those that aren't):

Load *

Resident PAYMENTS

where  DateKey >= YearStart( DateKey) and DateKey <= YearStart( DateKey)  and Frequency='Annually'  and recID > 0 and Month([Start Date])='Jan';

Load *

Resident PAYMENTS

where  DateKey >= YearStart( DateKey,0,num(month([Start Date]),'0')) and DateKey <= YearStart( DateKey,0,num(month([Start Date]),'0'))  and Frequency='Annually'  and recID > 0 and Month([Start Date])<>'Jan';

Follow- up question: Is there a way to handle half-year/semi-annual frequencies? I understand there's MonthStart, QuarterStart, YearStart functions to utilize but no I don't think I've seen a "HalfYearStart" (i.e. every 6 months) or similarly sounding.


For example:

Property KeyStart DateEnd DateCostFrequency
E0207/1/20186/30/20201000Semi-Annual

Resulting table should be:

Property KeyDateKeyPeriodCostYearMonth
E0207/1/201810002018-July
E0201/1/201910002019-Jan
E0207/1/201910002019-July
E0201/1/202010002020-Jan

I am guessing I'd have to leverage YearStart for this and have it somewhat go every 6 months instead of the full year. Any thoughts?

Siva_Sankar
Honored Contributor

Re: Load Script - break apart a schedule into individual months

Hi Mark,

As far as i know there is no predefined function for half year start, but you can calculate something mentioned here how do i generate year, Half yr, quarter, weekly &amp; day from this date format

-Thanks.

Siva

markramiro
Contributor II

Re: Load Script - break apart a schedule into individual months

I used the same methodology with what I did for Annual but did two sets and got it to work. The first set is basically exactly same as Annual. The second set of code is essentially the same except I moved it 6 months:

YearStart( DateKey,0,6)