topic Script help needed in QlikView

Script help needed

Wed, 15 Dec 2010 17:38:13 GMT

I'm tracking employee hires and terminations in a single graph by month. My script looked like this:

AttritionDate_1:
[Employee ID],
[Term Date] AS [Action Date],
[Term Year] AS [Action Year],
[Term Month] AS [Action Month]
RESIDENT Terminations;
LOAD
[Employee ID],
[Hire Date] AS [Action Date],
[Hire Year] AS [Action Year],
[Hire Month] AS [Action Month]
RESIDENT Hires;



AttritionDate:
NoConcatenate LOAD
[Employee ID],
[Action Month],
[Action Date],
[Action Year]
RESIDENT AttritionDate_1;
DROP TABLE AttritionDate_1;

This worked perfectly, however now I need to add in Requisitions as well. The problem is that the requisitions aren't attached to an Employee ID, they have a Requisition Number instead, which needs to be kept separate from Employee ID's. Any thoughts on how to change my script to take this into account?

I would have Requisition Number, Action Month, Action Date, Action Year. I'm sure there's something simple that I'm missing, but I can't figure it out and I'm on a deadline.

Script help needed

Wed, 15 Dec 2010 18:46:59 GMT

I think you need to have a Requisition Number field added to employee table .

Seems you want to show Requisitions by Employee, you have to have some relationship there.

Script help needed

Wed, 15 Dec 2010 20:12:40 GMT

The problem is that the requisitions aren't necessarily linked to an employee that is in the employee table. They are a separate entity all together. We have employee terms and hires and then want to compare requisitions that have been posted against this.

Script help needed

boorgura — Wed, 15 Dec 2010 20:39:01 GMT

For presentation, do you need to present them on a ID level.

or just an aggregate level.

I mean the total number for that month or day??

Script help needed

Wed, 15 Dec 2010 20:53:24 GMT

Just on an aggregate level. The big problem is I need to show them all in a line graph. 3 lines (hires, terms and requisitions) by month for the current year. So, I need a "month" field that can be used in the graph, while still keeping each date separate.

In the chart, the expressions are:

Terminations:

count(distinct if([Term Year]=vCurYear and [Term Month]=[Action Month], [Distinct EMPLID]))

Hires:

count(distinct if([Hire Year]=vCurYear and [Hire Month]=[Action Month], [Distinct EMPLID]))

Requisitions:

count(distinct if([Requisition Year]=vCurYear and [Requisition Month]=[Action Month], [Requisition Number]]))

Script help needed

Wed, 15 Dec 2010 20:56:22 GMT

Additionally, this system is slow and using vast amounts of memory, so if there's a way to steamline this, please advise!

Script help needed

boorgura — Wed, 15 Dec 2010 22:15:46 GMT

ok please try this:

just do a concatenate load of the requisition data.

use the field names as [action date] [action month] and [action year].

and the ID should be req ID/hire ID/ term ID

then chart can have

[action month] as dimension.

and the expressions will be something like:

count(${[action year] = "2010"} distinct <Term ID or Hire ID or Req ID>)

the ID should whatever the expression corresponds to.