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!
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):
FROM source.xls (biff, .... filters(Replace(1, top, StrCnd(null)))
to the load expression.
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.
Don't forget the single quotes around 'Store'.
I think this is very late reply.. But i saw this post today.
if(isnull(Store)=0,Store,Peek(store2, -1)) as store2
(ooxml, embedded labels, table is Sheet1);
DROP Field Store;
This will work fine
Let me know your concerns.