Help Moving a Set Analysis to Load Script

Wed, 24 Oct 2012 17:52:09 GMT

Hi Everyone,

I need some help moving this set analysis to the load script. So I will use the Employees table as Resident and I will want to group by CalendarMonthName. Can someone construct this Set Analysis in Load script? I am going to build this for each month and it is a count of employees here at the end of each month.

TEMP:

Load

if(ClanedarMonthName) = 'Jan',

Count(EmployeeDirectory.ID)

Where NLE_Period=vJanPer

That is as far as I get because of the subtraction and addition that gets to happen. This set analysis works for expressions and now I get to put the fields in the script so that I can build a trending by month compared to other values. So I want a statement that accomplishing the same thing for each month.

if (([CalendarMonthName])='Jan',(count({<NLE_Period={'$(vJanPer)'}>} EmployeeDirectory.ID))/(count(EmployeeDirectory.ID)-(Count ({<NLE_MonthEnd={'<=$(vJan)'},DOH_MonthEnd={'<=$(vJan)'}>} EmployeeDirectory.ID)+(count({<DOH_Period={'>$(vJanPer)'}>} EmployeeDirectory.ID))))

RESIDENT

Employees

High Regards to anyone who assists me,

OnePuttGirl

Re: Help Moving a Set Analysis to Load Script

Thu, 25 Oct 2012 03:48:47 GMT

Hi OnePuttGirl

Here's a potential solution.

If I have understood correctly you need to be able to calculate the number of employees you have in your business at the end of each month.

I will assume that your Employees table contains the following fields:

Employee ID
Employee Start Date
Employee Leave Date (Blank if employee is still employed)

You start by loading this table twice, once to capture the start dates and a second time to capture the leave dates. The added 'Type' field allows this distinction to be achieved. As part of this script you can establish common date fields with Month_Year being very useful for your particular analysis.

You then use the group by function in mapping tables to count the number of starters and leavers in each Month_Year.

Next you use the ApplyMap function to create a table that contains all the data you need in one place, i.e.

Month_Year
Count of Starters for each Month_Year
Count of Leavers for each Month_Year
Calculated balance of starters-leavers

Finally you need to set up a straight table adding the above-mentioned four fields as dimensions then use an expression to calculate the cumulative balance.

The expression for calculating the cumulative balance is:

rangesum(above(Total sum(Balance),0,RowNo(Total)))

The script to create the tables is below:

Employees:

LOAD ID,

[Start Date] as Date,

'Starters' as Type,

Month([Start Date]) as Month,

Year([Start Date]) as Year,

Date(MonthStart([Start Date]),'MMM-YYYY') as Month_Year

FROM

Employees.xls

(biff, embedded labels, table is Sheet1$);

Concatenate

LOAD

ID,

[Leave Date] as Date,

'Leavers' as Type,

Month([Leave Date]) as Month,

Year([Leave Date]) as Year,

Date(MonthStart([Leave Date]),'MMM-YYYY') as Month_Year

FROM

Employees.xls

(biff, embedded labels, table is Sheet1$);

Starters_Map:

Mapping Load

Month_Year,

Count(ID) as Count_of_Starters

Resident Employees

Where Type='Starters'

Group By Month_Year

;

Leavers_Map:

Mapping Load

Month_Year,

Count(ID) as Count_of_Leavers

Resident Employees

Where Type='Leavers'

Group By Month_Year

;

Results:

Load distinct

Month_Year,

ApplyMap('Starters_Map',Month_Year,0) as Starters,

ApplyMap('Leavers_Map',Month_Year,0) as Leavers,

ApplyMap('Starters_Map',Month_Year,0)-ApplyMap('Leavers_Map',Month_Year,0) as Balance

Resident Employees

Order By Month_Year Asc;

The output from this example is shown below:

Month_Year	Starters	Leavers	Balance	Cumulative Balance
Sep-2011	4	0	4	4
Oct-2011	4	0	4	8
Nov-2011	0	1	-1	7
Dec-2011	0	1	-1	6
Jan-2012	8	0	8	14
Feb-2012	3	1	2	16
Apr-2012	0	1	-1	15
May-2012	6	3	3	18
Jun-2012	0	1	-1	17
Jul-2012	0	1	-1	16
Aug-2012	0	1	-1	15
Sep-2012	5	8	-3	12
Oct-2012	0	1	-1	11

Hope that helps

Best wishes

Steve

topic Re: Help Moving a Set Analysis to Load Script in QlikView

Help Moving a Set Analysis to Load Script

Re: Help Moving a Set Analysis to Load Script