topic Re: Generate monthly historical data in QlikView

Generate monthly historical data

rogeriobarbosa — Wed, 25 Nov 2020 16:16:04 GMT

Hello, I have this data table

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

Re: Generate monthly historical data

Taoufiq_Zarra — Sat, 15 Feb 2020 22:40:02 GMT

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

Re: Generate monthly historical data

skiruthigadevi — Sat, 15 Feb 2020 23:08:22 GMT

may be this helps,

https://community.qlik.com/t5/Qlik-Design-Blog/Slowly-Changing-Dimensions/ba-p/1464187

Re: Generate monthly historical data

otaviofs — Mon, 17 Feb 2020 17:23:39 GMT

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;