Test2

Thu, 29 Oct 2015 12:30:00 GMT

adfa

Re: Three years tenure by grade

mvanlutterveld — Thu, 29 Oct 2015 14:39:58 GMT

Hi Naveen,

The best way to solve this, is to load your Excel file into QlikView and add some flag fields to the table. You can use these fields in the Set Analysis part of your expression.

Script can be something like:

Data:

LOAD Emp_code,

Grade,

Cal_year,

Active_flag

FROM [Three years tenure same grade.xlsx] (ooxml, embedded labels, table is Sheet1);

left Join(Data)

LOAD

Emp_code,

Count(distinct Grade) as #Grade

Resident Data

Group By Emp_code;

left Join(Data)

LOAD

Emp_code,

Count(Cal_year) as #Year

Resident Data

Where Active_flag = 1

Group By Emp_code;

RENAME Table Data to Data_TEMP;

Data:

NoConcatenate LOAD

If(#Grade > 1,0,1) as ind_SameGrade,

If(#Year > 3,1,0) as ind_ThreeYear

Resident Data_TEMP;

DROP Table Data_TEMP;

The 'ind_*' fields can be used as flag fields in your front end expression. Remember to perform a distinct count of the Emp_code.

Cheers,

Michiel

topic Test2 in QlikView

Test2

Re: Three years tenure by grade