Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
satishqlik
Creator II
Creator II

Kindly Suggest

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!!!

8 Replies
satishqlik
Creator II
Creator II
Author

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
Specialist
Specialist

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator II
Creator II
Author

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
Master
Master

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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!!!