topic show the latest available value in QlikView

show the latest available value

Mon, 25 Feb 2013 04:08:16 GMT

Hi,

I am tracking price fluctuations in products we purchase through our head office which are effected by volume based pricing.

Any items that have a price adjustment are listed in a file which is loaded into Qlikview. At present we have a few years worth of data listed by month and product.

If there is a blank field in a given month it means that there was no change in the price in that period.

I want to take the current table showing all the months and list only the most recent price regardless of which period it is in. Often the lastest period could be blank (no change) so I want it to go back to the last value and return that price.

Please see a mock up below, the top table is what we currently see, the bottom is what I'd like to also show.

Item	Months	Jan	Feb	Mar	Apr	May
Product1		1.2		1.27	1.24	1.25
Product2		5.26	5.33	5.21	5.28
Product3		3.49	3.53	3.44
Product4		0.59	0.61		0.64	0.63


Item	Latest Price
Product1	1.25
Product2	5.28
Product3	3.44
Product4	0.63

Thanks

David

Re: show the latest available value

phaneendra_kunc — Mon, 25 Feb 2013 04:49:02 GMT

See if the attached helps..

Incase if you cant open the attached...

UI Expression : FirstSortedValue(NEW_VAL,-Sort)

Dimension: Item

//Script....

Data:

load * inline [

Item,Month,Price

Product1,Jan,1.2

Product1,Feb,

Product1,Mar,1.27

Product1,Apr,1.24

Product1,May,1.25

Product2,Jan,5.26

Product2,Feb,5.33

Product2,Mar,5.21

Product2,Apr,5.28

Product2,May,

Product3,Jan,3.49

Product3,Feb,3.53

Product3,Mar,3.44

Product3,Apr,

Product3,May,

Product4,Jan,0.59

Product4,Feb,0.61

Product4,Mar,

Product4,Apr,0.63

Product4,May,0.64

];

Data_Transformed:

load *, IF (Item=PEEK('Item') AND len(Price)=0,PEEK('NEW_VAL'),Price) AS NEW_VAL Resident Data order by Item asc;

Drop table Data;

Month:

Load * Inline [

Month, Sort

Jan,1

Feb,2

Mar,3

Apr,4

May,5

];

Re: show the latest available value

Mon, 25 Feb 2013 05:53:26 GMT

try this

CrossTable(month,value)

LOAD item,

Jan,

Feb,

Mar,

Apr,

May

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD item,

MAX(date(DATE#(month,'MMM'),'MMM')) as max_month,

FirstSortedValue(value,-date(DATE#(month,'MMM'),'MMM')) as latest_price

Resident a

Group BY item ;

then output like this--

item	latest_price
p1	1.25
p2	5.28
p3	3.44
p4	0.63