Get previous row value when the value is null

[Anonymous]

Dear Experts,

I would like to copy the previous value when the current row value is null.

I tried the peek/previous functions but it is working only for the first blank row and it doesn't carry that logic to the next blank row.

I have Billing Lines and customers.For few billing lines I don't have the Customer details in which I want to copy the Customer number from the previous billing line.

Since the data is huge,could you please suggest the best way to achive this logic?

Thanks & Regards

Jeba

[Mark_Little]

Hi

Would is your logic you have used?

I would normally,do something like below

IF(RowNo() =1 ,

     IF(ISNULL(Value)=-1,0

          IF(ISNULL(Value)=-1, Previous(Value))

          )

     ) AS Value,

But that will only work for one step back, if you could have more than one value blank then you need to look at the peek and longer nested if.

Mark

[Anonymous]

Hi Mark,

The blank rows are dynamic and it will be very difficult to use nested if

Regards

Jeba

[Mark_Little]

Is you logic the same as mine.

As I was not thinking correctly as this works on loading the table it should basic work as

if the first value is null then 0

then it load the next line if it is null then it will also be 0 unless the value.

So it should always have a value to check back on.

Mark

[Peter_Cammaert]

Do not use previous(). Use peek() as you would like to propagate the previously propagated value from the resulting table (not the input table) into the next blank field.

LOAD ...

     IF (len(trim(Customer)) = 0, peek('Customer'), Customer) AS Customer,

     :

RESIDENT ...

Note that you may have to check for a very first row having a blank Customer.

Best,

Peter

[Anonymous]

Hi Peter,

Thanks for your reply.

The sample attachment will help you more about my issue.

I want to fill the highlighted yellow rows with the above filled value.The peek function copy only the next row and it doesn't go to the third row where it is blank.Also I am curious it works some time and doesn't work some times.

Kindly help

Thanks & Regads

Jeba

[Peter_Cammaert]

Then why does my code produce the following result?

[Anonymous]

Hi Peter,

I will try this again and confirm.

I was using isnull logic.was that wrong?

Regards

Jeba

[Peter_Cammaert]

IsNull() will work for database fields (databases know about the NULL concept) but not for Excel. Excel will return empty strings.

The trick len(trim(Fieldname)) will work in all cases.

[thomaslg_wq]

Hi,

Maybe you could use the fill transformation step function (don't know if it works with resident load but it does work on table files load).

LOAD [BILLING LINE],

     Customer

FROM

sample.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Replace(2, top, StrCnd(null))

));

Hope it helps,

Thomas