2 Replies Latest reply: Jul 10, 2014 3:07 AM by Friedrich Hofmann RSS

    Historization in QlikView - again...

    Friedrich Hofmann

      Hi,

       

      I have the following requirement:

      - From our personell_database, I get the daily status of how many employees we have and when they joined.

      - For reasons of functionality, I have to build a parallel database day_by_day  based on this.

      - Since all employees that I can get from the database are still with us, I just put a static '31.12.2100' as leaving_date.

      - On the next day, I load a new status from the database and do the following:

        - I have two tables now, one with the status from yesterday and one with the status from today;

        - I check which employees are there today who were not there yesterday, those are newbies

          => I put today's date as their joining_date and my static '31.12.2100' as leaving_date;

        - Then I check which employees are no more there today who were yesterday, those are the leavers

          => I put today's date as their leaving_date.

       

      Now I have four tables

      - a history (yesterday's status)

      - a new LOAD (today's status)

      - a small table with leavers (might be 0)

      - a small table with newbies (might be 0)

      => I can easily concatenate the newbies to the existing history and thus make a new, slightly bigger one that I can STORE in the old
           one's stead

      <=> The more problematic part is modifying the records for the leavers: I don't want to simply take them out of the table - that would
              annull what sense there is and make it no different from what I get from the database; Instead I want to keep them, but replace
              the static far-future leaving_date with today's date.
             => Tomorrow I will have to check whether anyone I identify as "leaver" already has a leaving_date so I don't end up overwriting
                   it with tomorrow's date.

       

      The question is, how can I do this?

      <=> I think I could do it in two steps:
               - First, I would remove the leavers from my history by way of a WHERE NOT EXISTS() clause
                - and then I would add them again from my small leavers_table where they already have a leaving_date

       

      Can anybody advise me on this or maybe propose an easier way?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler
            

        • Re: Historization in QlikView - again...
          whiteline _

          Hi.

           

          I think that you have relatively small data set in this case. What is the reason for complexity ?

          Anyway, there are two kinds of data: event data (enter/leave), status data. Why don't you just keep incremental reload for events (if needed) and always rebuild status data ?

          If you strongly want to do something more intelligent, you can rebuild status data only for those persons that have new events.

            • Re: Historization in QlikView - again...
              Friedrich Hofmann

              Hi whiteline,

               

              the reason I make it so complex is that our database simply does not provide the data I need to display:

              -> The day an employee leaves, we delete all associated data in the database - we still know he was here,

                  but nothing more - I cannot tell which area he worked in before he left. That data is also deleted for the past,

                  so someone who left virtually never belonged to any specific area ...

              <-> I need to show the areas, however, because otherwise there would be no value in having that info - the objective is to identify the areas with the greatest fluctuation.

              -> Partial reload would be great, but if I understand it correctly, that would not touch the personell_numbers (that would be my keyfield) which are already there, correct? - so they would keep their leaving_date of '31.12.2100' or so which they had earlier - assuming their leaving_date was not known when they joined. I need to update that with the date that they disappeared from the database.

               

              Best regards,

               

              DataNibbler