Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
AmirKilkr
Contributor
Contributor

start and end date

 
 

Hi friends,

I need your help please,

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;

0 Replies