Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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!
Hi,
upload sample application with sample data . i will solve ur problems
Regards
Perumal A
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
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
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.
Try with this
if(len(Trim(Store))=0, Peek(Store2), Store) AS Store2
Hope it helps