Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Distribute values from begin to and end contract date??

CustomerTotal (rental) value for 1 month Begin contract dateEnd Contract Date
1500031-12-201431-12-2015
220031-1-201531-12-2016

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

What I want is the following:

Customer Rental Sum monthDate
1500031-12-2014
1500031-1-2015
15000etc
1500031-12-2015
220031-1-2015
2

200

etc
220031-12-2016

Who can help me out?

6 Replies

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

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

- Marcus

stabben23
Honored Contributor

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

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;

rupe
Valued Contributor

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

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
Honored Contributor

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

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;

MVP
MVP

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

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
Honored Contributor

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

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

Community Browser