Create a qvd by year

[gauravgg]

Hi All ,

I have the following table .

Material , Vendor , Plant ,Valid from , Valid to  ,Price.

form the above table i have to create qvd   Year wise Price for Material ,Vendor ,Plant combination ie. as follows 

Material , Vendor,Plant , Year ,Price 

Thanks in Advance

[miskinmaz]

Hi,

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.

[Vegar]

Try something like this.

SET DateFormat='YYYY-MM-DD';
LOAD 
  Material ,
  Vendor ,
  Plant ,
  Price, 
  alt(Year( [Valid from]), year(today()) )+IterNo()-1 as Year 
inline [
  Material ,Vendor ,Plant ,Valid from ,Valid to, Price
  Pepsi, Pepsi, Filling, 2019-01-01, , 13
  Pepsi, Pepsi, Filling, 2018-01-01, 2018-12-31, 10
  Pepsi, Pepsi, Filling, 2016-01-01, 2017-12-31, 09
  Fanta, Pepsi, Filling, 2017-01-01, 2018-12-31, 11
  Fanta, Pepsi, Filling, 2019-01-01, 2019-12-31, 10
  Cola, CocaCola, Filling, 2018-01-01, , 10
  Cola, CocaCola, Filling, 2017-01-01, 2017-12-31, 09
  Zingo, CocaCola, Filling, 2018-01-01, 2019-12-31, 11
  Zingo, CocaCola, Filling, 2017-01-01, 2017-12-31, 10]
WHILE 
  alt(Year( [Valid from]), year(today()) )+IterNo()-1 <= Year([Valid to])

;

[gauravgg]

Hi Vegar ,

Thanks for your reply .

I tried running your code , but it takes many hours to reload  as my Valid to field contains date as 01/01/9999  for some materials , ie these material price is valid till date .

can you help me in modifying code  only last 3 FY years data .

[Vegar]

Try this:

SET DateFormat='YYYY-MM-DD';
LOAD 
	Material ,
	Vendor ,
	Plant ,
	Price  ,
	year([Valid from]) + IterNo()-1  as Year
inline [
        Material ,Vendor ,Plant ,Valid from ,Valid to, Price
	Pepsi, Pepsi, Filling, 2019-01-01, , 13
	Pepsi, Pepsi, Filling, 2010-01-01, 2018-12-31, 10
	Pepsi, Pepsi, Filling, 2016-01-01, 2017-12-31, 09
	Fanta, Pepsi, Filling, 2017-01-01, 2018-12-31, 11
	Fanta, Pepsi, Filling, 2019-01-01, 9999-12-31, 10
	Cola, CocaCola, Filling, 2008-01-01, 2016-12-31, 10
	Cola, CocaCola, Filling, 2017-01-01, 2017-12-31, 09
	Zingo, CocaCola, Filling, 2018-01-01, 2019-12-31, 11
	Zingo, CocaCola, Filling, 2017-01-01, 2017-12-31, 10]
WHILE 
	year([Valid from]) + IterNo()-1 <= rangemin(year([Valid to]) , year(today())+3)
;
	

[gauravgg]

Hi Vegar ,

i have an another acquirement ,

I have to only store price as on 31 march for all material,vendor , plant combination  ,and  in case of year filed it will be date which will have all years of 31-march  till today .

Regards

Gaurav Gopale  

[Vegar]

I'm not sure I understand, could you please adjust my input INLINE[] table above and provide a table/picture of you expected output?

[gauravgg]

Input table 
inline [
        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
	]

Output Table 
inline [
 Material ,Vendor ,Plant ,Date ,FiscalYear, Price 
  Pepsi, Pepsi, Filling, 2019-03-31,  2018 ,09
  Fanta, Pepsi, Filling, 2017-03-31, 2016, 10 
 Fanta, Pepsi, Filling, 2018-03-31, 2017, 10 
Fanta, Pepsi, Filling, 2019-03-31, 2018, 10 
]

[Vegar]

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 */
	FiscalList:
	LOAD 
	   DUAL($(_year)-1,Makedate($(_year),3,31) ) as FiscalYear 
	AutoGenerate 1;
NEXT


tmpPricelist:
LOAD  
   Material,
   Vendor,
   Plant,	
   [Valid from], 
   date(rangemin(date#([Valid to],'YYYY-MM-DD'), makedate(year(Today()),3,31))) as [Valid to], 
   Price 
inline [
   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.
IntervalMatch (FiscalYear)
LOAD [Valid from], [Valid to]
Resident tmpPricelist;

DROP TABLE FiscalList;


//Finalizes the Price list table
//Separates the year and date from the FiscalYear dual
PriceList:
LOAD 
   Material,
   Vendor,
   Plant,
   Text(FiscalYear) as FiscalYear, 
   dayname(FiscalYear) as Date,
   Price
RESIDENT tmpPricelist;

DROP Table tmpPricelist;