Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
A load with while and addmonths() should do your task: http://community.qlik.com/message/46208#46208
- Marcus
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;
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
;
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;
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;
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";