11 Replies Latest reply: Jan 13, 2015 4:32 PM by Petter Skjolden RSS

    Do While loop in script for monthy totals

    Mark Francis

      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

       

        • Re: Do While loop in script for monthy totals
          Elena Prandoni

          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

          • Re: Do While loop in script for monthy totals
            Petter Skjolden

            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

              • Re: Do While loop in script for monthy totals
                Mark Francis

                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

                 

                  • Re: Do While loop in script for monthy totals
                    Petter Skjolden

                    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...

                      • Re: Do While loop in script for monthy totals
                        Mark Francis

                        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.

                  • Re: Do While loop in script for monthy totals
                    Mark Francis

                    Are ok made the column's invisible did't know you could do that.

                     

                    So on your expression

                    SUM (TOTAL <ID> [Planned Hours])

                     

                    The  Planned Hours seems to be reference the expression label instead of the field name, am correct. ? If so

                    how do you know if you are refering to the lable or the field name

                     

                    Sorry for all the questions just a bit new to this.

                    Thanks

                     

                    Mark