Autogenerate field with Month Year at the same time

[jonathanrichey]

I would like to generate a row for every employee with a month year and populate the the missing tJobCode. I believe I have the tJobCode populating correctly, just not getting tEMP for every record when the date is autogenerated.

What is the best approach to generate a field with every date?  The field I am trying to generate with tMonthYear is the tEmp. Attached is the QVW as well. 

TempTable_HrHistory:
Load
EMPLOYEE AS tEmp,
MonthYear as tMonthYear,
JOB_CODE as tJobCode
Resident HRHISTORY;
//EXIT SCRIPT;


MinMaxDate:
Load Min(tMonthYear) as MinDate, Max(tMonthYear) as MaxDate resident TempTable_HrHistory;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
Drop Table MinMaxDate;

Join (TempTable_HrHistory)
Load date(MonthStart(recno()+$(vMinDate))) as tMonthYear
//tEmp
 Autogenerate vMaxDate - vMinDate;

//exit SCRIPT;

FinalHR:
NoConcatenate Load tMonthYear,tEmp,
If( IsNull( tJobCode ), Peek( tJobCode ), tJobCode ) as tJobCode
Resident TempTable_HrHistory
Order By tMonthYear ;
Drop Table TempTable_HrHistory;
DROP TABLE HRHISTORY;

[sunny_talwar]

Is this may be what you are looking for?

HRHISTORY:
LOAD *,
	 Date(MonthStart(Date),'MMM YYYY') as MonthYear;
LOAD * INLINE [
    Date, EMPLOYEE, JOB_CODE
    01/01/2017, 456, 20171
    06/01/2017, 456, 66666
    12/01/2018, 456, 20079
    02/01/2016, 333, 55555
    11/01/2018, 333, 20079
];

TempTable_HrHistory:
LOAD EMPLOYEE AS tEmp,
	 JOB_CODE as tJobCode
Resident HRHISTORY;

MinMaxDate:
LOAD Min(MonthYear) as MinDate,
	 Max(MonthYear) as MaxDate
Resident HRHISTORY;

LET vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
LET vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
DROP Table MinMaxDate;

Join (TempTable_HrHistory)
LOAD Date(MonthStart(RecNo() + $(vMinDate))) as tMonthYear
AutoGenerate vMaxDate - vMinDate;
DROP TABLE HRHISTORY;

[jonathanrichey]

No, Employee 333 has the earliest start date causing employee 456 to have a record on 02/01/2016. 

[sunny_talwar]

Check this may be

HRHISTORY:
LOAD *,
	 Date(MonthStart(Date),'MMM YYYY') as MonthYear;
LOAD * INLINE [
    Date, EMPLOYEE, JOB_CODE
    01/01/2017, 456, 20171
    06/01/2017, 456, 66666
    12/01/2018, 456, 20079
    02/01/2016, 333, 55555
    11/01/2018, 333, 20079
];

MinMaxDate:
LOAD Max(MonthYear) as MaxDate
Resident HRHISTORY;

LET vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
DROP Table MinMaxDate;

FinalHR:
LOAD DISTINCT EMPLOYEE,
	 JOB_CODE,
	 Date(MonthStart(MonthYear, IterNo() - 1)) as MonthYear
Resident HRHISTORY
While MonthStart(MonthYear, IterNo() - 1) <= $(vMaxDate);
DROP TABLE HRHISTORY;

[jonathanrichey]

Close, but now the issue is duplicate job codes. I was trying to take the approach of not populating the jobcode when the date is created. This would allow the peak function to handle the months that are null and populate with the most recent jobcode. 

If( IsNull( tJobCode ), Peek( tJobCode ), tJobCode ) as tJobCode

Looking back at the orignal code, Is it possible to autogenerate the tEmp with the load statement below?

Join (TempTable_HrHistory)
Load date(MonthStart(recno()+$(vMinDate))) as tMonthYear
,tEmp
 Autogenerate vMaxDate - vMinDate;

[sunny_talwar]

---

@jonathanrichey wrote:

Join (TempTable_HrHistory)
Load date(MonthStart(recno()+$(vMinDate))) as tMonthYear
,tEmp
 Autogenerate vMaxDate - vMinDate;

---

This is not possible because AutoGenerate cannot have a field from another table.

Taking a step back... what exactly do you need as an output? Would you be able to provide the required output in an Excel file based on the data you have provided in the sample?

[jonathanrichey]

Yes, attached is an excel sheet. 

[sunny_talwar · Accepted solution]

Try this

HRHISTORY:
LOAD * INLINE [
    Date, EMPLOYEE, JOB_CODE
    01/01/2017, 456, 20171
    06/01/2017, 456, 66666
    12/01/2018, 456, 20079
    02/01/2016, 333, 55555
    11/01/2018, 333, 20079
];

MinMaxDate:
LOAD Max(MonthStart(Date, 1)) as MaxDate
Resident HRHISTORY;

LET vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
DROP Table MinMaxDate;

TempTable:
LOAD *,
	 Date(If(EMPLOYEE = Previous(EMPLOYEE), Previous(Date), '$(vMaxDate)')) as EndDate
Resident HRHISTORY
Order By EMPLOYEE, Date desc;
DROP Table HRHISTORY;

FinalHR:
LOAD DISTINCT EMPLOYEE,
	 JOB_CODE,
	 Date(MonthStart(Date, IterNo() - 1)) as MonthYear
Resident TempTable
While MonthStart(Date, IterNo() - 1) < EndDate;

DROP Table TempTable;