Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
satishqlik
Contributor 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!!!

Tags (1)
8 Replies
satishqlik
Contributor II

Re: Kindly Suggest

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
Valued Contributor

Re: Kindly Suggest

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

;

MVP & Luminary
MVP & Luminary

Re: Kindly Suggest

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
Contributor II

Re: Kindly Suggest

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
Honored Contributor

Re: Kindly Suggest

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

MVP & Luminary
MVP & Luminary

Re: Kindly Suggest

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
Contributor II

Re: Kindly Suggest

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
Contributor II

Re: Kindly Suggest

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