Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Do While loop in script for monthy totals

Hi

Not sure where to start on this one.

Want to end up with the straight Table examples 1 and 2, but with the data calculated in the Script.

Straight Table example 1                          ID - A
14-0114-0214-0314-0414-0514-06Total
Planned Hours5101015101060
Actual Hours101512 37
Remaining Hours 3101023

Straight Table example 2                          ID - B
14-1114-1215-0115-0215-03 Total
Planned Hours100200200200200 900
Actual Hours 250 250
Remaining Hours 50200200200 650

Have Tasks and Resource which is combined into a ID as per ID - A and ID - B

For each ID there are Planned hours per Year-Month to complete the Task & Actual hours they have booked against the task per Year Month

What I need to calculate in the script is the Remaining Planned hours per Year – Month for each ID.

The remaining Planned hours per month cannot be greater than originally planned hours per Month.

As I start off with 5 million rows of raw data as it is stored in days in or ERP system. I have summed this up into a number of tables; the main ones I would use would be Planned Monthly Table and Actual Total Table as shown.

The Planned Monthly Table is sorted by ID and Year Month and has Row No, Total No Months and Total Planned hours.

Planned  Monthly Table
IDYear MonthPlanned HoursRow NoNo of MonthsTotal Planned Hours
A14-0151660
A14-02102660
A14-03103660
A14-04154660
A14-05105660
A14-06106660
B14-1110075900
B14-1220085900
B15-0120095900
B15-02200105900
B15-03200115900

The Actual Total Table just has the ID and the Total Actual Hours. There are other detailed tables but didn’t think they would be wanted demonstrate this example.

Actual Total Table
ID Total Actual
A 37
B

250

My thoughts were to calculate the delta between Total Planned and Total Actual (A = 23) (B = 650)

Then loop through each Month starting at the last month deducting the planned hours from the remaining hours, until the remaining hours are zero, then loop to the next ID. Some ID’s could be over 120 months (10 Years)

A =       23 – 10 = 13

            13 – 10 = 3

            3

B =       650 – 200 = 450

            450 – 200 = 250

            250 – 200 = 50

            50

Sometimes the Actual > than planned so I would filter the table first so I don’t have to loop these then combine them later.

Hope all this makes sense I know this isn’t a 5 minute answer but need some help putting the loop together and moving form 1 ID to the next. So any help would be much appreciated.

Thanks

Mark

11 Replies
Highlighted
Specialist
Specialist

Hi Mark,

I don't understand how to calculate Actual Hours (i.e. values 10, 15, 12 in 14-01, 14-02 and 14-03 in first table shown).

Furthermore, it would be very helpfull if you showed the expected result (such as the final table).

Thanks

Highlighted
Not applicable

HI Elena

Thanks for you're interest the Actual hours are known facts it's the remaining hours are ones to be calculated,  I will upload the expected result in the morning.

Highlighted
MVP
MVP

I couldn't help thinking that the solution is much easier to come up with using Pivot Table so I made a solution not solving the entire transformation in the Load Script. Anyhow several things had to be done in the Load Script to get the proper data model for a Pivot Table.A pure Load Script solution will get up to 120 months as columns  ... which is hardly optimal for a QlikView data model...The proposed solution gives you tremendously more flexibility as you can dynamically include or exclude columns from the pivot only by selecting from a listbox for [Year Month].

2015-01-11 Actual Planned PIVOT.PNG

Highlighted
Not applicable

Hi thanks for your various replies please find enclosed my expected results, in bar graph showing the remaining hours.

The important thing I need the Remaining Hours to be calculated.

This calculation is based on the following that needs to be in some sort of loop for each ID working backwards from the last month.

E.g Total Planned – Total Actual = X and Last Month = Dec

Then if X > Planned Hours of Dec, Dec Remaining Hours = Planned Hours and X = X –Planned Hours,

Then if X > Planned Hours Nov, Nov Remaining Hours = Planned Hours and X = X – Planned Hours,

And so on until

Else Remaining hours = Planned Hours – X

Then when X = 0 Loop to the next ID.

Hope this makes sense.

Thanks

Mark

Highlighted
MVP
MVP

I am sorry ... It doesn't make any sense to me. Your explanation of how to calculate I mean.

Tell me do you want to have this all calculated in the load script? If yes - why?

If you want some more help I will have to ask you to give me an example of a real calculation in a spreadsheet that works. Then I will be able to replicate it in QlikView without spending a lot of time trying to understand any explanations...

Highlighted
Not applicable

HI Petter

My reason for calculating in the load script is because my real data is 5,000,000 plus rows as the info comes from an ERP system and I though it would be more efficient To do the calculation in the script.

I would also  struggle to do the loop calculation in Excel.

I realise my explanation is difficult to understand, but if you see my original posting the result of the remaining hours might make more sense.

Basiclly if the total Actual Hours are less that the total planned hours Then the remaining monthly hours can only be the samed as the original planned hours per month except the 1st month with remaining hours could be less. So total Remaining hours + Total Actual hours = Total Planned Hours.

Highlighted
MVP
MVP

Well - after having studied your description and your test data I managed to understand the logic of it. Actually 5 million rows is no match for QlikView so I would go for the in-memory calculation approach instead of a Load Script calculation.A load script approach should actually also be quite straight-forward I think (but I will not cover that here). I would advise you to have a data model with all both Planned and Actual Hours in the same table which will speed up calculation too.

The final expressions turned out to be rather straight forward:

2015-01-13 Qlik 03.PNG

2015-01-13 Qlik 04.PNG

2015-01-13 Qlik 01.PNG

Highlighted
Not applicable

Thanks Petter thats a great  help a lot more simpler than I thought.

Just looking at combining Remaining Hours' and Total Hours into one expression as I only want to display the Remaining Hours in the bar Chart.

Highlighted
MVP
MVP

You can just hide the column - it is probably much better performance wise instead of repeating the calculation three times while embedding it into a single expression....