topic Re: Split the billing by months based on the frequency level in pivot table in QlikView

Split the billing by months based on the frequency level in pivot table

Anonymous — Wed, 25 Jul 2018 03:45:30 GMT

Hi Team,

I am working on the requirement, requirement is to plot on which month a particular policy@ is due for billing and to identify the billing Frequency of a particular policy and plot on which month the billing will fall. You will base the starting billing month on the Date value (consider month only).

Available values for Frequency are 1, 2, 4, 12

1 = only 1 billing for the whole year

2 = bi yearly billing (every 6 months)

4 = quarterly billing

12 = every month billing

Attached the source data and the required output.

Note: Logic should be implement in script level,

This is the critical issue. Can any one help me on this. Thanks.

Re: Split the billing by months based on the frequency level in pivot table

pavansonu99 — Wed, 25 Jul 2018 08:13:10 GMT

Try this

this will help you

Source:
LOAD * INLINE [
Policy No, Date, Frequency
101, 4/3/2015, 12
101, 6/5/2015, 1
101, 2/3/2015, 4
101, 7/5/2015, 4
101, 4/4/2015, 2
101, 7/7/2015, 2
];

Finel:
load *,
AutoNumberHash128([Policy No]&'-'&Date&'-'&Frequency) as %Key,
Date(AddMonths(MonthStart(Date),(IterNo()-1)*(12/Frequency) ),'MMM') as Month

Resident Source
while IterNo()<=Frequency;
;

Drop Table Source

Re: Split the billing by months based on the frequency level in pivot table

Anonymous — Wed, 25 Jul 2018 13:46:32 GMT

Hi Pavan,

Thanks for the reply, when i have multiple policy numbers the billing frequency for months getting null and billing value (1) , So months are repeating to show the billing frequency. Only Feb & April values coming has the issue. the values has to come to arrow placed position,since the 'null' is presenting in that same place for feb & April displaying two times to represent the value '1'. Please find the issue screen below

Re: Split the billing by months based on the frequency level in pivot table

pavansonu99 — Wed, 25 Jul 2018 16:31:32 GMT

Source:

LOAD * INLINE [

Policy No, Date, Frequency

101, 4/3/2015, 12

102, 6/5/2015, 1

103, 2/3/2015, 4

104, 7/5/2015, 4

105, 4/4/2015, 2

106, 7/7/2015, 2

];

Finel:

load *,

Date(AddMonths(MonthStart(Date),(IterNo()-1)*(12/Frequency)),'MMM') as Mon

Resident Source

while IterNo()<=Frequency;

table:

load [Policy No], Date, Frequency,

date(SetDateYear(Mon,15),'MMM') as Month

Resident Finel;

Drop Table Source,Finel;

Re: Split the billing by months based on the frequency level in pivot table

Anonymous — Wed, 25 Jul 2018 16:49:21 GMT

Thanks a lot Pavan.