Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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
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") 😉
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