Hi Sunny,
Please, see below what I have done so far.
TABLE_P:
LOAD * INLINE [
ID, REGION, Product, Launch Date, START DATE, END DATE
1, A, pineapple, 10/02/2015, 10/02/2015, 09/02/2020
2, B, pineapple, 10/02/2016, 10/02/2016, 09/02/2022
3, C, pineapple, 12/02/2018, 12/02/2018, 11/02/2020
4, A, apple, 10/02/2013, 10/02/2013, 09/02/2017
5, B, apple, 15/05/2016, 15/05/2016, 14/05/2023
6, C, apple, 16/05/2016, 16/05/2016, 15/05/2017
7, A, pineapple, 05/05/2015, ,
8, B, apple, 25/02/2018, ,
9, C, Mango, 22/05/2017, ,
10, A, Mango, 26/05/2020, ,
];
TABLE_C:
Load REGION as REG_A,
count( DISTINCT ID) as QTY_PROD,
YEAR([Launch Date]) as YR_Launch
resident TABLE_P group by YEAR([Launch Date]), REGION;
//OUTSIDE_COUNT
TABLE_COUNT:
load *,
IF(Previous(REG_A)=REG_A, if(len(peek(QTY_ACC))>0, peek(QTY_ACC), Previous(QTY_PROD)) + QTY_PROD) as QTY_ACC
Resident TABLE_C Order by REG_A, YR_Launch;
drop Table TABLE_C;
LOAD * INLINE [
YEAR_STT, NUMBER
2015, 1
2016, 1
2017, 1
2018, 1
2019, 1
2020, 1
2021, 1
2022, 1
2023, 1
2024, 1
2025, 1
2026, 1
2027, 1
2028, 1
2029, 1
2030, 1
2031, 1
];
//INSIDE_COUNT
TABLE_INTERVAL:
NoConcatenate
load
year([START DATE]) as INTERVAL_START,
year([END DATE]) as INTERVAL_END,
REGION as REG_A,
ID
Resident TABLE_P;
DATA:
IntervalMatch(YEAR_STT) load
INTERVAL_START, INTERVAL_END
Resident TABLE_INTERVAL;
