Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
sravankv
Partner
Partner

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

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

View solution in original 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.)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes