Previous Function Help

[edg_baltazar]

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!

[perumal_41]

Hi

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

Try this function

Regards

Perumal

[edg_baltazar]

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!

[perumal_41]

Hi,

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

Regards

Perumal A

[rwunderlich]

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

[jonathandienst]

Hi

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

[#7]

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

[CELAMBARASAN]

Try with this

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

Hope it helps