Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a main data set with financials. Every record has an ID which corresponds with a employee
I have a second data set with Employee ID's and teams they belong to with a start and end date (Some times they move teams). What I need is add the team to the main data set with the team name they belong to based on the date off the record.
For example if in January they belong to team A, all records for that employee in January would need to have team A.
Looking forward to your help
Regards, Paul
Hi Paul, You can try this:
Finance:
LOAD ID,
Amount,
Date,
ProjectName
FROM
(ooxml, embedded labels, table is [Financial Data]);
employee:
LOAD
ID,
date(StartTeam) as StartTeam,
if(isnull(date(EndTeam)),date(today()),date(EndTeam)) as EndTeam,
TeamName,
MainTeamID
FROM
(ooxml, embedded labels, table is Employee);
join IntervalMatch(Date) Load distinct StartTeam , EndTeam resident employee;
join (employee) load
Date,
Amount,
ProjectName
Resident Finance;
drop table Finance;
Could you share your dummy data and provide the expected results?
Hi,
See attached.
The first sheet is the employee data. The second sheet the financial data. Third sheet is what I need.
What I need is to add the column for team name and main team ID to the financial data. What I expect is that the records from january to august get the team name Watt and main team id FCO. And the records from september to december get the team name Sherlocks and main team id FCM. This is because he switched teams which you can see in the employee tab in the date columns
Thanks for the help
Hi Paul, You can try this:
Finance:
LOAD ID,
Amount,
Date,
ProjectName
FROM
(ooxml, embedded labels, table is [Financial Data]);
employee:
LOAD
ID,
date(StartTeam) as StartTeam,
if(isnull(date(EndTeam)),date(today()),date(EndTeam)) as EndTeam,
TeamName,
MainTeamID
FROM
(ooxml, embedded labels, table is Employee);
join IntervalMatch(Date) Load distinct StartTeam , EndTeam resident employee;
join (employee) load
Date,
Amount,
ProjectName
Resident Finance;
drop table Finance;
Hello Paul,
Hope you are doing great!
You can make use of INNER JOIN and Resident Load Functionality to resolve this issue.
Use below script get effective Department of every employee:
//Load the base table i.e. Financials and join it with EmpDept.
BaseTrans:
LOAD
EmpId,
"Date" AS TranDate,
Name,
Sales
FROM [lib://AttachedFiles/TestData.xlsx]
(ooxml, embedded labels, table is Financials);
INNER JOIN
LOAD
EmpId,
EffectiveFrom,
EffectiveTo,
Department
FROM [lib://AttachedFiles/TestData.xlsx]
(ooxml, embedded labels, table is EmpDept);
//Load the Trans table by referring BaseTrans and filter the records
//based on TranDate between EffectiveFrom AND EffectiveTo
Trans:
LOAD
EmpId,
TranDate,
Name,
Sales,
Department
Resident BaseTrans
WHERE (TranDate >= EffectiveFrom AND (TranDate <= EffectiveTo OR Len(EffectiveTo)=0));
DROP TABLE BaseTrans;
Hope this will help.
Thank you!
Rahul