I have 2 tables called EmpHier and EmpDeparture. in the EmpHier each Salesmen have a few StartDate.
I need to make one table in which I need to have Stardate to each Salesman and an EndDate.
Information:
Few salesmen have multiple Startdate cuz they changed departments and that is why I used the Previous function.
In case StartDate is null- I want that the end date will be from the 'TO Date' from the EmpDeparture filed
If TO Date' from the EmpDeparture and if null - Today's date.
If you can assist and explain to me what I did wrong.
Thank you,
EmpHierTemp1:
LOAD SalesmanID, Department, Manager, StartDate, Date(alt(date#(StartDate, 'DD/MM/YYYY'), num(StartDate))) as StartDateTest
FROM [lib://DBF/EmpHier.qvd] (qvd);
EmpHierTemp2: LOAD RowNo() as RowNumber, SalesmanID, Manager, //, StartDate, StartDateTest Resident EmpHierTemp1 order by SalesmanID,StartDateTest ; Drop Table EmpHierTemp1;
EmpHier: LOAD RowNumber, SalesmanID, Manager, //???, StartDateTest, if(SalesmanID=Previous(SalesmanID),(Previous(StartDateTest)-1),today()+9) as ToDateNum _____________________________________________________________________________________ Resident EmpHierTemp2 order by RowNumber desc;
Left join (EmpHier) //EmpDeparture: LOAD SalesmanID, "To Date" FROM [lib://DBF/EmpDeparture.qvd] (qvd);
Store EmpHier into [lib://DBF//Temp/EmpHier.qvd]; Drop Table EmpHierTemp2;
