topic Re: Kindly Suggest in QlikView

Kindly Suggest

satishqlik — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Kindly Suggest

satishqlik — Mon, 28 Aug 2017 18:02:07 GMT

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.

Re: Kindly Suggest

el_aprendiz111 — Mon, 28 Aug 2017 22:16:59 GMT

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

;

Re: Kindly Suggest

rwunderlich — Mon, 28 Aug 2017 23:51:11 GMT

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

Re: Kindly Suggest

satishqlik — Tue, 29 Aug 2017 04:51:10 GMT

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.

Re: Kindly Suggest

effinty2112 — Tue, 29 Aug 2017 07:41:58 GMT

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

Re: Kindly Suggest

rwunderlich — Tue, 29 Aug 2017 16:13:15 GMT

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

-Rob

Re: Kindly Suggest

satishqlik — Tue, 29 Aug 2017 16:51:29 GMT

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

Re: Kindly Suggest

satishqlik — Tue, 29 Aug 2017 16:54:48 GMT

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