Add field between dates

[pauldamen]

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

[MindaugasBacius]

Could you share your dummy data and provide the expected results?

[pauldamen]

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

[stabben23 · Accepted solution]

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;

[rahulpawarb]

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