Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
sravankv
Partner - Contributor III
Partner - Contributor III

Data Mapping Issue

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_NoEmp_Start_DateCountry
10017/05/2008UK
10004/02/2009US
10009/07/2010AUS

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

Emp_NoIncident NumIncident_Created_date
1001017/05/2007
1002017/05/2007
1003031/07/2007
1004007/05/2008
1002311/06/2008
1003211/06/2008
1001112/01/2009
1002104/02/2009
1003114/01/2010
1002418/06/2010
1005309/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_NoIncident NumIncident_Created_dateCountry
1001017/05/2007UK
1002017/05/2007UK
1003031/07/2007UK
1004007/05/2008UK
1002311/06/2008US
1003211/06/2008US
1001112/01/2009US
1002104/02/2009US
1003114/01/2010AUS
1002418/06/2010AUS
1005309/07/2010AUS

 

Thanks in Advance,

Labels (2)
1 Solution

Accepted Solutions
MayilVahanan

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:

MayilVahanan_0-1632458553304.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

2 Replies
MayilVahanan

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:

MayilVahanan_0-1632458553304.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Vegar
MVP
MVP

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.)