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
