topic Re: Data from database - not populated on weekends in QlikView

Data from database - not populated on weekends

datanibbler — Mon, 09 Dec 2013 16:00:26 GMT

Hi,

in one diagram where I want to display employee_presence data, I

- the "nr_of_emps_associated_with_an_area" as the nr. of emps who should be present - which isn't quite correct because the fact
that they belong to an area doesn't say anything about their being present, but that's how it is currently.

- The nr. of reports I find in the database of an employee reporting in sick as just that

=> My current problem is: The employees are of course associated with an area every day from Mon-Sun

<=> The out_sick_reports in the database are populated only from Mon to Sat

=> If I were to display six days in a week, as I usually do (for we work quite often on Sat), I would end up with 100% presence every Sat, which of course would mess up my quota

=> What I want to do now to fix this is display the same nr. of sick_reports that I had on a Fri for the Sat of that week.

=> I guess I would have to do that in the script since I cannot use set_expressions in a diagram to shift the dimensions?

Can anyone help me do this?

Thanks a lot!

Best regards,

DataNibbler

Re: Data from database - not populated on weekends

Anonymous — Tue, 10 Dec 2013 06:32:09 GMT

Hi,

Can you post a sample app. That way it will be easier...

Regards,

Aadil

Re: Data from database - not populated on weekends

datanibbler — Tue, 10 Dec 2013 09:52:39 GMT

Hi Aadil,

a sample app in this case is a bit tricky - I'll have to build one from scratch as this chart is about personell data. I'll do that some time today or tonight on the train.

The problem is actually quite simple:

- My presence_data (the nr._of_emps_in_a_given_area) is available on Saturdays

- The data in the personell_database of employees reporting out sick is not

=> Of course, when I basically subtract the latter from the former and divide it by the former (A-B / A),

I get a quota of 100% which messes up my average for the week.

I'll have a look at the table and see if I can do it myself - I think the data at this point is sorted by date and there is one record per day - per employee. The only problem is there isn't any data for Saturdays - the records are not in the database, so I have to create those records in the QlikView script.

Thanks a lot!

Best regards,

DataNibbler

Re: Data from database - not populated on weekends

Anonymous — Tue, 10 Dec 2013 12:00:10 GMT

I guess, you got it right. That should work. Good Luck.

Re: Data from database - not populated on weekends

datanibbler — Tue, 10 Dec 2013 13:21:08 GMT

Yep, I guess - I hope so ...

The question is merely: How do I generate additional records in a QlikView_script?

I know it's in the help_file, I just don't know the word to look for.

Can you help me there?

Thanks a lot!

Best regards,

DataNibbler

Re: Data from database - not populated on weekends

datanibbler — Wed, 11 Dec 2013 08:41:25 GMT

Hi Aadil,

I think I found a way. Have yet to test it, though.

I don't even have to actually generate those records:

- That info from the database is lateron joined to another table

- That table is already "blown up" to have one record per day per employee

=> I can just sort it in a RESIDENT LOAD and then use PREVIOUS to populate that field on every "saturday_record" with a 0 or 1, whatever was there in the record above, which must then be Friday.

Hope it works. I'll be back with a short debrief.

Best regards,

DataNibbler