topic Re: Add field between dates in QlikView

Add field between dates

pauldamen — Wed, 11 Jan 2017 10:26:53 GMT

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

Re: Add field between dates

MindaugasBacius — Wed, 11 Jan 2017 10:34:47 GMT

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

Re: Add field between dates

pauldamen — Wed, 11 Jan 2017 10:44:16 GMT

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

Re: Add field between dates

stabben23 — Wed, 11 Jan 2017 11:09:41 GMT

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;

Re: Add field between dates

rahulpawarb — Wed, 11 Jan 2017 11:54:25 GMT

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