3 Replies Latest reply: Jul 26, 2013 7:27 AM by Friedrich Hofmann RSS

    Personell data, holidays - formula help needed

    Friedrich Hofmann

      hi,

       

      I need some formula help here:

       

      My ultimate goal is to construct a diagram with the nr. of days an employee has neither taken nor planned yet.

       

      - When I select a personell_number, I get, from a SELECT on a database table, a number of days - that person's "holiday contingent" at year's start.

      - <=> The thing is, these days are distributed: There are three or four (it varies) different budgets (another field) and I have to add them all up.

       

      - In another table, I have the days taken until today and in a third table, I have the days that person has already planned.

      - The third table (days planned) is easy as I have to filter on one specific  budget anyway.

       

      - <=> The trick in the second table (days taken) is: I have several budgets again - a few more than in the first table. I have to select the two (or three or four) that are "selectable" in the first table, then I get the correct number - but I have to do it on scripting level as I want to build a diagram - so set_analysis is out of bounds anyway.

       

      Can anyone help me there? I will start on the first part - I can do a RESIDENT load and sum up the days a given employee has.

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: Personell data, holidays - formula help needed
          Friedrich Hofmann

          Hi,

           

          maybe I can firstly have two separate LOAD statements - one for the holiday_budget, one for the days_taken - and then do two RESIDENT loads and join those`using a LEFT JOIN?

          The question is - those two tables should be joined on the PN and the budget_type - so I have to rename the budget_type to have the same name in both tables, right?

            • Re: Personell data, holidays - formula help needed
              Friedrich Hofmann

              Hi,

               

              that seems to WORK. Yippieh!

               

              O_O - not quite:

              Probably due to those JOINs - I have joined the RESIDENT loads from three tables now for

              - the entire holiday_budget

              - days_taken and

              - days_planned

              I can no more get the correct nr. of days - still, when I select a PN, a number of budgets are "selectable" and several numbers of days which, summed up, give me the correct value - but when I sum them up in a textbox, I get much, much more. Somewhere along the way, the records have multiplied.

               

              P.S.:

              ...and it is logical that they would ;-) In the first table (holiday budget), there are, say, three records (for three different budgets) with three values - in the other tables (days taken and days planned), there is one record for every day and I have to count date_values.

              I will do some more trying around.

                • Re: Personell data, holidays - formula help needed
                  Friedrich Hofmann

                  Hi,

                   

                  now I'm almost there. I introduced one more RESIDENT load for the table where I have days_taken, summing up by PN and budget_type and counting the respective nr. of days.

                  I do the same with the third table where I have days_planned.

                  Now I have one last problem:

                   

                  In the very first table, I have three records - three different budgets with different nrs of days which I have to sum up. I could do one more RESIDENT load and sum these up beforehand, but I need all three budgets to filter (through the JOIN) the second table - that one has more budget_types.

                  I do a RESIDENT load again for the third table and get the correct value - but when I join this to what I have, one record becomes three records (three budget_types) and I have three times the nr of days.

                  Nr_lines is 1 when it should be 3 - because I have 3 different budget_types in the original table.

                  The trouble is, in the LOAD statement I don't yet "know" there are 3  budget_types - there might be more, depending on which PN I select.

                  Somehow I have to reduce it somewhere along the way - after I have done all that I need the different budget_types for - to just one record per PN.

                   

                  Thanks a lot!

                  Best regards,

                   

                  DataNibbler