Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to make a maintence budget.
Startyear is 2013 and end year 2042 (overview of 30 years).
There are different activities with there own cycle.
Example:
Activity 1: each 5 years pump inspection, cost EUR 1.000
Activity 2: each 8 years tank maintenance, cost EUR 3.500
Activity 3: .....
Table:
Activity; Cycle; Cost
I want to see in a pivot table the cost into the year in reference with the cycle.
Activity Year 2018 2021 2023 2028 2029 2033 2037 ...... 2042
Pump inspection 1.000 1000 1.000 1.000
Tank maintenance 3.500 3.500 3.500
Is it possible that qlikview calculate the year in order to the startdate and cycle
Ralph
Ralph,
Add the statement from swuehl in your excel load.
Let vStart = 2013;
Let vEnd = 2042;
Budget:
LOAD
Activity,
Cost,
Asset,
Interval,
$(vStart)+iterno()*Interval as Year
FROM
[..\..\..\Examlpe\Brondata.xlsx]
(ooxml, embedded labels, table is Budget)
while $(vStart) + iterno()*Interval <= $(vEnd);
Maybe like this:
Let vStart = 2013;
Let vEnd = 2042;
LOAD Activity, Cost, Interval, $(vStart)+iterno()*Interval as Year INLINE [
Activity, Cost, Interval
Pump Inspection, 1000, 5
Tank Maintenance, 3500,8
] while $(vStart) + iterno()*Interval <= $(vEnd);
to create your maintenance costs over time, then create a simple pivot table in the front end.
Hello swuehl,
Thank you for the quick respond.
That's exactly what i'm looking for but I don't have an inline table but a data table.
Budget:
LOAD
Activity,
Cost,
Asset,
Interval,
FROM
[..\..\..\Examlpe\Brondata.xlsx]
(ooxml, embedded labels, table is Budget);
So I have to find out how I get this into you're script.
Ralph
Ralph,
Add the statement from swuehl in your excel load.
Let vStart = 2013;
Let vEnd = 2042;
Budget:
LOAD
Activity,
Cost,
Asset,
Interval,
$(vStart)+iterno()*Interval as Year
FROM
[..\..\..\Examlpe\Brondata.xlsx]
(ooxml, embedded labels, table is Budget)
while $(vStart) + iterno()*Interval <= $(vEnd);
Yet another solution:
Years:
LOAD
rowno()+$(vStartYear)-1 as DateYear
AutoGenerate(30);
left join(Years)
LOAD
rowno() as Interval
AutoGenerate(30);
Activities:
NoConcatenate
LOAD
DateYear,
Interval
Resident Years
Where mod(DateYear-$(vStartYear)+1, Interval)=0;
drop table Years;
right join (Activities)
LOAD * INLINE [
Activity, Cost, Interval
Pump Inspection, 1000, 5
Tank Maintenance, 3500,8
];