Below is algo for your req: 1. Create a temp table with Year dimension. 2. for loop from 0 to no.of rows of temp table. 3. set varaible with a peek of year from the temp table. 4. write where exits condition of year with the varaible while reloading the table. 5. store the table with the variable.
You can solve this by using interval match on a list of 31 March dates. This script transform your sample data into the desired output.
FOR _year = year(today())-3 to year(today())
/* A table containing a dual with fiscal year as text representation */
DUAL($(_year)-1,Makedate($(_year),3,31) ) as FiscalYear
date(rangemin(date#([Valid to],'YYYY-MM-DD'), makedate(year(Today()),3,31))) as [Valid to],
Material ,Vendor ,Plant , Valid from , Valid to, Price
Pepsi, Pepsi, Filling, 2019-01-01, 2019-01-20,30
Pepsi, Pepsi, Filling, 2019-01-21, 2019-02-25, 10
Pepsi, Pepsi, Filling, 2019-02-26, 9999-03-26, 09
Fanta, Pepsi, Filling, 2017-01-01, 2017-01-28, 11
Fanta, Pepsi, Filling, 2017-01-30, 9999-12-31, 10
inner JOIN (tmpPricelist) //You only want to keep data in the pricelist interval.
LOAD [Valid from], [Valid to]
DROP TABLE FiscalList;
//Finalizes the Price list table
//Separates the year and date from the FiscalYear dual
Text(FiscalYear) as FiscalYear,
dayname(FiscalYear) as Date,
DROP Table tmpPricelist;