Medium: Ratio for Foreign worker

Sat, 17 Apr 2010 18:52:52 GMT

Dear all,
I really need help this condition with your expertise.
My company required me to do ratio for Foreign Worker.
Our company can hire/recruit foreign worker base from local worker ratio.
So the ratio should be like this:

Local Worker= 1
Foreign Worker= 7
Ratio= 1:7

And company have 3 type of industry:

Construction (Local= 5, NTS worker= 35)
Marine (Local= 2, NTS worker= 14)
Maintenance (Local= 10, NTS worker= 70)

Ratio will be taken from excel, reason easy to modify just in case there change ratio.
Each individual local worker will able starting contributing when after 3 month (not day) from employed date e.g. sample local employe below:

Status: Inactive
Name: Micheal Doe
EmploytDate: 15-02-2009
TerminationDate: 30-07-2009
*Contribute since xx-05-2009 until xx-10-2009 (No longer contribute)

Status: Inactive
Name: John Smith
EmployDate: 21-01-2008
ResignDate: 30-02-2010
*Contribute since xx-04-2008 until xx-06-2010 (Still-contribute)

Status: Inactive
Name: Marco Seth
EmployDate: 08-02-2009
ResignDate: 30-04-2010
*Contribute since xx-05-2009 until xx-07-2010 (still-contribute)

Status: Active
Name: Smith White
EmployDate: 12-08-2009
*Contribute since xx-11-2009 until preset

Status: Active
Name: Catherine
EmployDate: 01-04-2010
*Non-active-Contribute
*Contribute will start at xx-07-2010

TodayDate: 18-04-2010

Case like John and Marco still contribute even they still counts 3months after resign date or termination date.

Company did not see base on date but on 3 month.
And this 3 months not probation period, even probation period extended to 4month-6month not effected, still 3 month.

And we need to know each month table, how many total foreign worker company can available can recruit.

Such like this:


Industry |Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec|
-------------------------------------------------------------
Construction |
Marine |
Maintenance |

and my script look like this


SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MMM-YYYY hh:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET Num='';
SET MONTH='MMM';

LET CurrentDate = Date(today(0));
LET CurrentMonth = Month(today(0));
LET CurrentYear = Year(today(0));

Employee:
LOAD
 "No_" as EE,
 "First Name",
 Date("Employment Date") as EmployDate,
 Month("Employment Date") as EmpMonth,
 Year("Employment Date") as EmpYear,

 Date("Termination Date") as TerminDate,
 Month("Termination Date") as TerminMonth,
 Year("Termination Date") as TerminYear,

 Date("Resignation Date") as ResignDate,
 Month("Resignation Date") as ResignMonth,
 Year("Resignation Date") as ResignYear;

SQL SELECT *
FROM ENSURE.dbo."DATABASE$Employee"

Medium: Ratio for Foreign worker

Mon, 19 Apr 2010 07:38:36 GMT

Hi Frank,

If you could attach a sample application with some data that will be great.

Regards

Rajesh

Medium: Ratio for Foreign worker

Tue, 20 Apr 2010 04:17:50 GMT

Dear Rajesh,
Actually is data is simple.

Loading from MySQL database.
It just like this:

LOAD
No_ as EE,
Employment Date as EmployDate,
Resignation Date as ResignDate,
Termination Date as TerminDate,
Inactive Date as InactvDate

SQL SELECT *
FROM ENSURE.dbo."SQLDATABASE$Employee"
where ("No_" Between '2' and '5999');

Someone can help on this?

Medium: Ratio for Foreign worker

Tue, 20 Apr 2010 06:36:52 GMT

Hi Frank,

Herewith i have attached the application for your reference. Pls post me if you find any difficulties.

Regards

Rajesh

Medium: Ratio for Foreign worker

Tue, 20 Apr 2010 11:30:28 GMT

Hi Rajesh,
I still find difficulties...

Anyone can help about this? 😞

Frenky

topic Medium: Ratio for Foreign worker in QlikView

Medium: Ratio for Foreign worker

Medium: Ratio for Foreign worker

Medium: Ratio for Foreign worker

Medium: Ratio for Foreign worker

Medium: Ratio for Foreign worker