Thanks Kush for your reply. I am sure i missed out some things in my code, below is the code which using.
Step 1: Intially loaded all the data
LOAD *, (termination_date-hire_date) as TotalEmpDays; select * from emp where emp_place not in ('ABC','XYZ');
step 2: just loaded with max refresh date and joined
hierarchy(emp_id,manager_id,manager_name) Load emp_id,manager_id,manager_name; select emp_id,manager_id,manager_name from emp where refresh date in (select max(refresh date) from emp) and where emp_id<> '100010' and status <> 'Exit';
Here becoz of max refresh date am unable to get old emp information when i use hierachy.
When i try to use the pivot table its showing only latest information becoz of joining the max refresh date . I have to fix this. But not getting the exact info for fix.
LOAD [Manager ID],
[Emp Join date],
[Refresh Date] as Refresh_Date
LOAD [Emp Id],
count([Emp Id]) as Emp_cnt
Group by [Emp Id];
LOAD date(max(Refresh_Date)) as Max_Refresh_Date;
LOAD FieldValue('Refresh_Date',RecNo()) as Refresh_Date
if([Emp Id]<>Peek([Emp Id]),Refresh_Date,date(peek(Refresh_Date)-1)),Max_Refresh_Date) as Max_Date
order by [Emp Id],Refresh_Date desc;
DROP Table Data;
date(Refresh_Date+IterNo()-1) as [Refresh Date]
LOAD Distinct Refresh_Date as [Refresh Date]
DROP Table T1;
Drop Fields Refresh_Date,Max_Refresh_Date,Emp_cnt,Max_Date;
Now you can try creating pivot table with expression as count(distinct EmpID) or count(EmpID)
hi, looke like field name reference is wrong in your code. change field name. In my example I used 'Refresh_Date' but you need to use field name in your data. If it is [ Refresh Date] then use 'Refresh Date' in fieldvalue and fieldvaluecount function