topic Re: Distribute values from begin to and end contract date?? in QlikView

Distribute values from begin to and end contract date??

Tue, 03 Mar 2015 09:44:29 GMT

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?

Re: Distribute values from begin to and end contract date??

marcus_sommer — Tue, 03 Mar 2015 13:01:10 GMT

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

- Marcus

Re: Distribute values from begin to and end contract date??

stabben23 — Tue, 03 Mar 2015 16:36:14 GMT

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;

Re: Distribute values from begin to and end contract date??

Roop — Tue, 03 Mar 2015 17:00:17 GMT

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
;

Re: Distribute values from begin to and end contract date??

stabben23 — Tue, 03 Mar 2015 18:16:19 GMT

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;

Re: Distribute values from begin to and end contract date??

maxgro — Tue, 03 Mar 2015 18:38:46 GMT

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

While

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

Re: Distribute values from begin to and end contract date??

stabben23 — Tue, 03 Mar 2015 18:49:15 GMT

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";