Kindly Suggest

[satishqlik]

Hi Experts,

I'm sharing my dummy data and it contains

Emp,Date,Logintime,Gender,Route,Logintype

This scenario is actually

To allocate the cab for group of employees(1 cab for 4 employees) which is based on the

route and date and logintype and logtime and gender.

How do we achieve this(frontend/backend)???

Any suggestions will be appreciated.

PFB for your reference!!!

[satishqlik]

I have tried with creating flags using If condition

Load *

If(count(Emp)/4=mod(count(Emp),4),'Flag1') as MyFlag

Resident maindata

Groupby

Logintype,Date,Logintime,Gender,Route;

But it is not working .....

Any suggestions will be appreciated.

[el_aprendiz111]

Hi

I did not quite understand what you want to do, check this out if it works

LOAD
count(Emp) AS EmpCount,
If(count(Emp)/4=mod(count(Emp),3),'Flag1') as MyFlag,
Date,
Login_time,
Gender,
Route,
Login_Type
FROM
Routemap.xls
(biff, embedded labels, table is Sheet1$)
Group by

Date,
Login_time,
Gender,
Route,
Login_Type

;

[rwunderlich]

Here is logic that works for one day. It would need to be adjusted to do multiple days at once. It would also need adjustment if you wanted to reuse Cabs across different login_times. See attached QVW.

EmpRoute:

LOAD

Emp,

    Date,

    Login_time,

    Gender,

    Route,

    Login_Type

FROM

Routemap.xls

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

CabAssign:

LOAD

*,

Route & '-' & (div(AutoNumber(Emp, Date & Route & Login_time)-1, 4)+1) as Cab

Resident EmpRoute

Order By Date, Route, Login_time, Emp

;

DROP TABLE EmpRoute;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[satishqlik]

That's nice really helpful.

How do we handle this situation if we have different dates and different login times like(6:30am,16:30,22:30)??

In the above source data only contains 1-mar-2017 and login time 6:30am

But in my actual data having different dates and different login times like(6:30am,16:30,22:30)

In this situation how it will work kindly suggest.

[effinty2112]

Hi Satish,

Maybe:

Data:

LOAD Emp,

     Date,

     Login_time,

     Gender,

     Route,

     Login_Type

FROM

Routemap.xls

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

NoConcatenate

OrderedData:

Load

GroupID & '-' & Div(3+Autonumber(Emp,GroupID),4) as CabID,

*;

LOAD

AutoNumber(Date & '|' & Login_time & '|' & Gender & '|' & Route & '|' & Login_Type,'GroupID') as GroupID,

Emp,

Date,

Login_time,

Gender,

Route,

Login_Type

Resident Data Order by Date,Route,Gender,Login_Type,Login_time;

DROP Table Data;

regards

Andrew

[rwunderlich]

The script should work as-is for multiple dates and login times. It already groups by those items.  The Cab# will keep incrementing.

-Rob

[satishqlik]

Yes Rob, I have checked that is working fine so far.

Also I need small clarification,In addition to that requirement,

We have allocated cab for group of employees which is based on

Date and Login_time and Route and Gender

Generally we are allocating one Escort Guard for each cab(If have all Female employees)

To avoid this(Escord Guard),

If all female employees in cab than how to allocate should have at-least one male employee ???? 

This is I would need to show.Is it possible to achieve???

Any suggestions will be appreciated.

Thanks in advance!!!

[satishqlik]

Hey Andrew thanks for response,

This is also really helpful and working fine so far.

But In addition to that requirement,I need small clarification,

We have allocated cab for group of employees which is based on

Date and Login_time and Route and Gender

Generally we are allocating one Escort Guard for each cab(If have all Female employees)

To avoid this(Escord Guard),

If all female employees in cab than how to allocate should have at-least one male employee ???? 

This is I would need to show.Is it possible to achieve???

Any suggestions will be appreciated.

Thanks in advance!!!