Distribute values from begin to and end contract date??

[#1]

Customer	Total (rental) value for 1 month	Begin contract date	End Contract Date
1	5000	31-12-2014	31-12-2015
2	200	31-1-2015	31-12-2016

The above is the information that I have in Qlikview at the moment.

What I want is the following:

Customer	Rental Sum month	Date
1	5000	31-12-2014
1	5000	31-1-2015
1	5000	etc
1	5000	31-12-2015
2	200	31-1-2015
2	200	etc
2	200	31-12-2016

Who can help me out?

[marcus_sommer]

A load with while and addmonths() should do your task: http://community.qlik.com/message/46208#46208

- Marcus

[stabben23]

Hi,

Create a "ReferenceDate" like this:  date(Begin contract date + IterNo()-1) as ReferenceDate,

and a While Begin contract date + IterNo() <= End contract Date+1;

[Roop]

ControlTable:
LOAD Cust,
Value,
Begin,
End
FROM
testBook2.xlsx
(ooxml, embedded labels, table is Sheet1);

MainTable:
load
Cust,
Value,
AddMonths(Begin, IterNo() - 1) as GeneratedMonth
resident ControlTable
WHILE AddMonths(Begin, IterNo() - 1) <= End
;

[stabben23]

Temp:
LOAD * Inline [
Customer,Value,"Begin contract date","End Contract Date"
1, 5000, 31-12-2014, 31-12-2015
2, 200, 31-01-2015, 31-12-2016
];

NoConcatenate
Temp2:
LOAD
Customer,
min("Begin contract date") as "Begin contract date",
max("End Contract Date") as "End Contract Date",
sum(Value) as Value
Resident Temp
group by Customer
order by Customer, "Begin contract date";

Temp3:
LOAD
Customer,
"Begin contract date",
"End Contract Date",
date("Begin contract date" + IterNo()-1) as ReferenceDate,
Value
Resident Temp2
While "Begin contract date" + IterNo() <= "End Contract Date"+1
;

Drop Table Temp, Temp2;

[maxgro]

PFA and change the names of the fields

SET DateFormat='DD-MM-YYYY';

s2:

load * inline [

c ,v, begin, end

1, 5000, 31-12-2014, 31-12-2015

2, 200, 31-1-2015, 31-12-2016

];

s3:

load

  *,

  date(begin + IterNo()-1) as d

Resident

  s2

While

  (begin + IterNo()-1) <= end;

[stabben23]

My second Temp2 is if you have several contract periodes on the same customer.

Temp2:
LOAD
Customer,
min("Begin contract date") as "Begin contract date",
max("End Contract Date") as "End Contract Date",
sum(Value) as Value
Resident Temp
group by Customer
order by Customer, "Begin contract date";