Historization in QlikView - again...

[datanibbler]

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
      

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

[datanibbler]

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