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: 
rogeriobarbosa
Contributor II
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

 

2 Solutions

Accepted Solutions
otaviofs
Contributor
Contributor

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;

View solution in original post

3 Replies
Taoufiq_Zarra

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
otaviofs
Contributor
Contributor

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;