Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Date | EmployeeID |
---|---|
12/2016 | 1 |
1/2017 | 1 |
2/2017 | 1 |
3/2017 | 1 |
Table2
Table 1
Month Date | EmployeeID | JobCode |
---|---|---|
12/2016 | 1 | A |
2/2017 | 1 | B |
3/2017 | 1 | 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
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;
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.
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:
Worked Perfectly. Thanks you so much for the help and your time. Appreciated
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
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:
result:
marley1978 ; did this do the trick?
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;
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 :
and sort as follow: