topic Re: Previous Function Help in QlikView

Previous Function Help

edg_baltazar — Tue, 20 Nov 2012 03:57:19 GMT

Hi! , I have this problem ... in the next table i would like to fill the Store field with the Previous Store value since I found a new value...

Store	Amount
A	100
	200
	400
B	50
	100
	70

I try the expression

if(isnull(Store)=0,Store,Previous(Store))

as [Store 2],

but I only works for the next register not for all, I get something like this...

Store2	Amount
A	100
A	200
	400
B	50
B	100
	70

Any ideas? , hope you can help me , Thanks in advance!

Re: Previous Function Help

perumal_41 — Tue, 20 Nov 2012 04:43:37 GMT

if(isnull(Store)=0 and ,Store,if(isnull(Previous(Store))=0,Previous(Store),Peek([Store 2]))) as [Store 2],

Try this function

Regards

Perumal

Re: Previous Function Help

edg_baltazar — Tue, 20 Nov 2012 05:43:48 GMT

Thanks for your answer Perumal A ! , but what im expecting is to fill all the null values in the Store field with the last value that was different to null(), in this case the result should be...

Store	Amount
A	100
A	200
A	400
B	50
B	100
B	70

But this can be dynamic maybe for store C i would have 100 null fields... that's the real problem, Regards!

Re: Previous Function Help

perumal_41 — Tue, 20 Nov 2012 05:56:51 GMT

Hi,

upload sample application with sample data . i will solve ur problems

Regards

Perumal A

Re: Previous Function Help

rwunderlich — Tue, 20 Nov 2012 06:23:26 GMT

Previous() fetches from the input table, so you will not get your changed value. Instead you want to use Peek(), which fetches from the output table.

if(isnull(Store)=0,Store,Peek('Store'))

Don't forget the single quotes around 'Store'.

-Rob

http://robwunderlich.com

Re: Previous Function Help

jonathandienst — Tue, 20 Nov 2012 06:32:26 GMT

The easiest way to do this is to click the "Enable Tranformation Step" when using the file load wizard and then using the "Fill" option to fill in the blanks.

This will add the following filter expression (bold text):

LOAD

...

FROM source.xls (biff, .... filters(Replace(1, top, StrCnd(null)))

to the load expression.

Regards

Jonathan

Re: Previous Function Help

Fri, 23 Aug 2013 12:58:32 GMT

Hello Edgar,

I think this is very late reply.. But i saw this post today.

Try this:

LOAD Store,

Amount,

if(isnull(Store)=0,Store,Peek(store2, -1)) as store2

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

DROP Field Store;

This will work fine

Let me know your concerns.

Rob Wunderlich

Re: Previous Function Help

CELAMBARASAN — Fri, 23 Aug 2013 13:39:17 GMT

Try with this

if(len(Trim(Store))=0, Peek(Store2), Store) AS Store2

Hope it helps