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: 
jonathanrichey
Contributor III
Contributor III

Autogenerate field with Month Year at the same time

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;

 

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

7 Replies
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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

Yes, attached is an excel sheet. 

sunny_talwar

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;