Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Highlighted
rogeriobarbosa
New Contributor II

Generate monthly historical data

Hello, I have this data table

Tab 01.png

 

 

I want to generate monthly data observing the occupation change date, how to do it in the script so that I have this result:

tab 02.png

 

3 Replies
Highlighted
Taoufiq_ZARRA
Valued Contributor

Re: Generate monthly historical data

can he have more than two occupations? or always 2 as in the example


Taoufiq ZARRA
Highlighted
Partner
Partner

Re: Generate monthly historical data

Highlighted
otaviofs
New Contributor

Re: Generate monthly historical data

Base:
LOAD
Name,
Occupation,
Change_Date as End,
date(if(Name=peek(Name),previous(Change_Date),yearstart(Change_Date))) as Start
;
LOAD * INLINE [
Name, Occupation, Change_Date
Jhon, Programmer, 01/03/2019
Jhon, Program Analyst, 15/06/2019
Jhon, Manager, 10/08/2019
];


Base_1:
Load
*,
date((Start +iterno()-1)) as Date
Resident Base
While Start +iterno()-1 < End;

drop table Base;