Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

marley1978
New Contributor II

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

Tags (1)
15 Replies
omarbensalem
Esteemed Contributor

Re: Join a table where the max value is less than or equal to joined 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 ];

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
New Contributor II

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

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
Esteemed Contributor

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

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
New Contributor II

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

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

marley1978
New Contributor II

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

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
Esteemed Contributor

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

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
Esteemed Contributor

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

marley1978 ; did this do the trick?

marley1978
New Contributor II

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

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
Esteemed Contributor

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

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