topic Re: Problem in multi selection of years in QlikView

Problem in multi selection of years

Wed, 25 Nov 2020 16:16:04 GMT

I want display worked hours per EMP_ID month wise , year wise and multi selection of months and years alone.
employee worked hours enter daily basis
here condition is worked hours <4 is available,>=4&<8 is partially available,>=8 is occupied.
could you please tell me script level

Regards

Niranjan

Re: Problem in multi selection of years

zhadrakas — Wed, 18 Jan 2017 13:59:19 GMT

Hello Niranjan,

try this

Source_Data:
LOAD * INLINE [
EMP_ID, WorkedHours, SheetDate
101, 2, '1/11/2015'
102, 3, '2/12/2015'
103, 4, '3/13/2015'
104, 5, '4/13/2015'
105, 6, '5/14/2015'
106, 7, '6/15/2015'
107, 8, '7/16/2015'
];

Load EMP_ID,
WorkedHours,
if(WorkedHours>=8, 'occupied',
if(WorkedHours>=4, 'partially available',
if(WorkedHours>0, 'is available'))) as EMP_STATE,
Date(SheetDate) as SheetDate,
year(SheetDate) as Year,
month(SheetDate) as Month
Resident Source_Data;

drop table Source_Data;

regards

Tim

Re: Problem in multi selection of years

Wed, 18 Jan 2017 14:57:52 GMT

Tim Driller,

when i select particular month(Feb) need to consider all Feb month for all years and finally showing that month employee status(available or partially available or occupied)

Re: Problem in multi selection of years

zhadrakas — Thu, 19 Jan 2017 14:38:57 GMT

Hello niranjan,

i dont understand what you want to achieve.

do you want to sum the workedhours for alle "Feb" Values?

Like this?

EMP_ID	WorkedHours	Month	EMP_STATE
101	6	Feb	partially available

Re: Problem in multi selection of years

praveenkumar_s — Thu, 19 Jan 2017 14:45:21 GMT

Tim Driller,

worked hours are 6 and divided by 2 because of two days worked finally worked hours 3 i.e EMP_State is Available

based on condition

Re: Problem in multi selection of years

vishsaggi — Thu, 19 Jan 2017 14:56:07 GMT

Hello Niranjan,

There is a similar topic here with the same request and similar text and Image but a different person named Yuvaraj G. I was confused , were you both work together or is this the same person? Just wondering?.

Problem in multi selections in years

Re: Problem in multi selection of years

zhadrakas — Thu, 19 Jan 2017 15:04:42 GMT

check if this is what you want...

regards

tim

Re: Problem in multi selection of years

Thu, 19 Jan 2017 15:46:26 GMT

he is my colleague

Re: Problem in multi selection of years

Thu, 19 Jan 2017 15:47:51 GMT

that qvw is not opening because i am using personal edition

Re: Problem in multi selection of years

vishsaggi — Thu, 19 Jan 2017 15:49:15 GMT

Ok, got it. Just thought would ask. Did you find the solution yet? Can you give us more information on your expected output or a sample app you are working on would help?

Re: Problem in multi selection of years

Thu, 19 Jan 2017 16:05:11 GMT

Here single selection working properly. Condition is t_worked hrs is < 4 available, >4&<8 is partially available and >8 is occupied. See first table EMP_ID 101 worked hours are 4+4=8 month is FEB

Round (sum (Workedhours)/ (count (DISTINCT (Sheetdate)))) as T_Worked
Resident ppp Group by EMP_ID, monthname;

See second table 101 employee data showing correctly

Upto now I used script is

ppp:
LOAD EMP_ID,
Workedhours,
Sheetdate,
MonthName(Sheetdate)as monthname,
Year(Sheetdate)as year
FROM

(ooxml, embedded labels, table is Sheet1);

Totalmonth:
LOAD
EMP_ID,
monthname,
Round(sum(Workedhours)/(count(DISTINCT(Sheetdate)))) as T_Worked
Resident ppp Group by EMP_ID,monthname;

New:
load *,
if(T_Worked<4,'Available',if(T_Worked>=4 and T_Worked<8,'Partially_available',if(T_Worked>=8,'occupied')))as Allocationlevel resident Totalmonth;

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(Sheetdate) as minDate,
max(Sheetdate) as maxDate
Resident ppp;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS Sheetdate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Problem is explained below

When I select Feb month need average T_worked for all years respective of month but now showing wrong

And when I select multiple years employee come under the one particular thing like available or partially available or occupied

Re: Problem in multi selection of years

Anonymous — Thu, 19 Jan 2017 16:19:39 GMT

I think you need to have a month name of just Feb instead of Feb 2015 so that all selected years get calculated together.

And then have your calculation for Available or Partially Available in your straight table as an expression that refers to the hours Column.

Re: Problem in multi selection of years

Thu, 19 Jan 2017 16:24:35 GMT

Here single selection working properly. Condition is t_worked hrs is < 4 available, >4&<8 is partially available and >8 is occupied. See first table EMP_ID 101 worked hours are 4+4=8 month is FEB

Round (sum (Workedhours)/ (count (DISTINCT (Sheetdate)))) as T_Worked
Resident ppp Group by EMP_ID, monthname;

See second table 101 employee data showing correctly

Upto now I used script is

ppp:
LOAD EMP_ID,
Workedhours,
Sheetdate,
MonthName(Sheetdate)as monthname,
Year(Sheetdate)as year
FROM

(ooxml, embedded labels, table is Sheet1);

Totalmonth:
LOAD
EMP_ID,
monthname,
Round(sum(Workedhours)/(count(DISTINCT(Sheetdate)))) as T_Worked
Resident ppp Group by EMP_ID,monthname;

New:
load *,
if(T_Worked<4,'Available',if(T_Worked>=4 andT_Worked<8,'Partially_available',if(T_Worked>=8,'occupied')))asAllocationlevel resident Totalmonth;

Problem is explained below

When I select Feb month need average T_worked for all years respective of month but now showing wrong

And when I select multiple years employee come under the one particular thing like available or partially available or occupied