Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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
Master II
Master II

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;

View solution in original post