Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
gauravgg
Partner - Creator
Partner - Creator

Create a qvd by year

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

 

 

Labels (2)
8 Replies
miskinmaz
Creator III
Creator III

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
MVP
MVP

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
Partner - Creator
Partner - Creator
Author

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
MVP
MVP

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
Partner - Creator
Partner - Creator
Author

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
MVP
MVP

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
Partner - Creator
Partner - Creator
Author

 

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
MVP
MVP

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;