Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marley1978
Contributor III
Contributor III

Join a table where the max value is less than or equal to joined table

Hi,  I have spent most of the day searching for a solution and cant seem to find anything.

I am trying to determine an employees position code based on a point in time

My tables are

Table1

Month DateEmployeeID
12/20161
1/20171
2/20171
3/20171

Table2

Table 1

Month DateEmployeeIDJobCode
12/20161A
2/20171B
3/20171

C

I want to display for 1/2017 that the employee jobcode is still "A" and since 1/2017 is not in table2 I cant get it to work

Thanks in advance

15 Replies
OmarBenSalem

Table1:

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID Inline [

MonthDate, EmployeeID

12/2016, 1

1/2017, 1

2/2017, 1

3/2017, 1 ];

Left Join(Table1)

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID, JobCode Inline [

MonthDate, EmployeeID, JobCode

12/2016, 1, A

2/2017, 1, B

3/2017, 1,   C

];

NoConcatenate

FinalTable:

load

MonthDate, EmployeeID,if (len(Trim(JobCode))=0,Peek(JobCode),JobCode) as JobCode  Resident

Table1 Order By MonthDate ;

drop table Table1;

Capture.PNG

marley1978
Contributor III
Contributor III
Author

Thanks but that is not what I am looking for.

I want January to be JobCode "A" since he didn't get to B until February.

OmarBenSalem

My bad !

Table1:

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID Inline [

MonthDate, EmployeeID

12/2016, 1

1/2017, 1

2/2017, 1

3/2017, 1 ];

Left Join(Table1)

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID, JobCode Inline [

MonthDate, EmployeeID, JobCode

12/2016, 1, A

2/2017, 1, B

3/2017, 1,   C

];

NoConcatenate

FinalTable:

load *, num(MonthDate) as numMonth  ;

load

MonthDate, EmployeeID, JobCode  Resident Table1 ;

drop table Table1;

NoConcatenate

final:

load MonthDate, EmployeeID,numMonth, if (len(Trim(JobCode))=0,Peek(JobCode),JobCode) as JobCode Resident FinalTable Order by numMonth;

drop field numMonth;

Drop Table FinalTable;

result:

Capture.PNG

marley1978
Contributor III
Contributor III
Author

Worked Perfectly.  Thanks you so much for the help and your time.  Appreciated

marley1978
Contributor III
Contributor III
Author

Omar,

Perhaps you can help me here.

I tried with more data and it doesn't result in the same.  I tested to see with a record with the employee having a history record with a date less than the main table.  What happens is that the left join excludes the prior records so I don't know what the job code is.

Table1:

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID Inline [

MonthDate, EmployeeID

12/2016, 1

1/2017, 1

2/2017, 1

3/2017, 1
12/2016, 2

1/2017, 2

2/2017, 2

3/2017, 2 ];

Left Join(Table1)

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID, JobCode Inline [

MonthDate, EmployeeID, JobCode

12/2012, 1, A

2/2015, 1, A

3/2017, 1,  C

12/2016, 2, A

3/2017, 2,   C

];

NoConcatenate

FinalTable:

load *, num(MonthDate) as numMonth  ;

load

MonthDate, EmployeeID, JobCode  Resident Table1 ;

drop table Table1;

NoConcatenate

final:

load MonthDate, EmployeeID,numMonth, if (len(Trim(JobCode))=0,Peek(JobCode),JobCode) as JobCode Resident FinalTable Order by EmployeeID, numMonth;

drop field numMonth;

Drop Table FinalTable;

Please see script

OmarBenSalem

then in ur table:

Table1:

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID Inline [

MonthDate, EmployeeID

12/2016, 1

1/2017, 1

2/2017, 1

3/2017, 1

12/2016, 2

1/2017, 2

2/2017, 2

3/2017, 2 ];

Left Join(Table1)

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID, JobCode Inline [

MonthDate, EmployeeID, JobCode

12/2012, 1, A

2/2015, 1, A

3/2017, 1,  C

12/2016, 2, A

3/2017, 2,   C

];

NoConcatenate

FinalTable:

load *, num(MonthDate) as numMonth  ;

load

MonthDate, EmployeeID, JobCode  Resident Table1 ;

drop table Table1;

NoConcatenate

final:

load MonthDate, EmployeeID,numMonth, if (len(Trim(JobCode))=0,Peek(JobCode),JobCode) as JobCode Resident FinalTable Order by numMonth;

drop field numMonth;

Drop Table FinalTable;

then in ur table:

as dimension: EmployeeID

MonthDate

as measure:

=if(IsNull( JobCode),below(only((JobCode))),JobCode)


and sort as follow:

Capture.PNG

result:

Capture.PNG

Capture.PNG

Capture.PNG

OmarBenSalem

marley1978 ; did this do the trick?

marley1978
Contributor III
Contributor III
Author

Thanks but that solution doesn't work.   If I put the job code for employee 2 as Z and it  makes the Z for job code for employee 1.

I think the cause is because you removed employee ID for sort order Order by numMonth;

Table1:

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID Inline [

MonthDate, EmployeeID

12/2016, 1

1/2017, 1

2/2017, 1

3/2017, 1

12/2016, 2

1/2017, 2

2/2017, 2

3/2017, 2 ];

Left Join(Table1)

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate, EmployeeID, JobCode Inline [

MonthDate, EmployeeID, JobCode

12/2012, 1, A

2/2015, 1, A

3/2017, 1,  C

12/2016, 2,Z

3/2017, 2,   C

];

NoConcatenate

FinalTable:

load *, num(MonthDate) as numMonth  ;

load

MonthDate, EmployeeID, JobCode  Resident Table1 ;

drop table Table1;

NoConcatenate

final:

load MonthDate, EmployeeID,numMonth, if (len(Trim(JobCode))=0,Peek(JobCode),JobCode) as JobCode Resident FinalTable Order by numMonth;

drop field numMonth;

Drop Table FinalTable;

OmarBenSalem

can u try this?

Table1:

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate,

date(Date#(MonthDate,'MM/YYYY')) as NewDate,

EmployeeID Inline [

MonthDate, EmployeeID

12/2016, 1

1/2017, 1

2/2017, 1

3/2017, 1

12/2016, 2

1/2017, 2

2/2017, 2

3/2017, 2 ];

Join(Table1)

load Capitalize(date(Date#(MonthDate,'MM/YYYY'),'MMMM YYYY')) as MonthDate,

date(Date#(MonthDate,'MM/YYYY')) as NewDate,

EmployeeID, JobCode Inline [

MonthDate, EmployeeID, JobCode

12/2012, 1, A

2/2015, 1, A

3/2017, 1,  C

12/2016, 2,Z

3/2017, 2,   C

];

NoConcatenate

FinalTable:

load *, num(NewDate) as numMonth  ;

load

MonthDate,NewDate, EmployeeID, JobCode  Resident Table1 ;

drop table Table1;

NoConcatenate

final:

load MonthDate, EmployeeID,numMonth,

if (len(Trim(JobCode))=0,Peek(JobCode),JobCode) as JobCode

Resident FinalTable Order by numMonth;

Drop Table FinalTable;

then in ur table: as dimenions, ur  dimenions :

Capture.PNG

and sort as follow:

Capture.PNG