Data Mapping Issue

[sravankv]

Hi All,

Can some help me with below scenario..

I have 2 tables 

1. Employ table with employ no, country and date(date when he was present in that country).

Emp_No	Emp_Start_Date	Country
100	17/05/2008	UK
100	04/02/2009	US
100	09/07/2010	AUS

  2. Insider table with employ no, incident number(issues he worked on) and incident_created_date(when that issue was raised).

Emp_No	Incident Num	Incident_Created_date
100	10	17/05/2007
100	20	17/05/2007
100	30	31/07/2007
100	40	07/05/2008
100	23	11/06/2008
100	32	11/06/2008
100	11	12/01/2009
100	21	04/02/2009
100	31	14/01/2010
100	24	18/06/2010
100	53	09/07/2010

Now i want to join both the tables and get Country field in Insider table. 

Based on Incident_Created_date <= Emp_Start_Date

i.e.  for Incident_Created_date <= 17/05/2008 Country should be UK

         Incident_Created_date <= 04/02/2009 Country should be US

        Incident_Created_date <= 09/07/2010 Country should be AUS

Emp_No	Incident Num	Incident_Created_date	Country
100	10	17/05/2007	UK
100	20	17/05/2007	UK
100	30	31/07/2007	UK
100	40	07/05/2008	UK
100	23	11/06/2008	US
100	32	11/06/2008	US
100	11	12/01/2009	US
100	21	04/02/2009	US
100	31	14/01/2010	AUS
100	24	18/06/2010	AUS
100	53	09/07/2010	AUS

Thanks in Advance,

[MayilVahanan · Accepted solution]

HI 

Try like below

Emp:
LOAD *,Emp_No&'|'& Floor(Emp_Start_Date) as Key INLINE [
Emp_No, Emp_Start_Date, Country
100, 17/05/2008, UK
100, 04/02/2009, US
100, 09/07/2010, AUS
];
Inc:
LOAD * INLINE [
Emp_No, Incident Num, Incident_Created_date
100, 10, 17/05/2007
100, 20, 17/05/2007
100, 30, 31/07/2007
100, 40, 07/05/2008
100, 23, 11/06/2008
100, 32, 11/06/2008
100, 11, 12/01/2009
100, 21, 04/02/2009
100, 31, 14/01/2010
100, 24, 18/06/2010
100, 53, 09/07/2010
];

Join(Inc)
Load Distinct Emp_No, Emp_Start_Date as Date1 Resident Emp;

IncTemp:
Load *, If(Incident_Created_date <= Date1, 1, 0) as Flag Resident Inc where Incident_Created_date <= Date1;

Join(Emp)
Load Emp_No, [Incident Num], Incident_Created_date, Emp_No&'|'&min(Date1) as Key Resident IncTemp Group by Emp_No, [Incident Num], Incident_Created_date;

DROP Table Inc, IncTemp;
DROP Field Key;

O/P:

[Vegar]

You should figure out a way to define the interval for each Employment, you need to calculate Emp_End_Date. When you have done that then you can solve your issue by using IntervalMatch. (Alternatively by looping your emp table and generate a row for each date the employment was active.)