How to repeat previous numeric value in loading script

[marcel_olmo]

Hi guys,

I'm trying to read a public database of invoicing per companies, and some of them doesn't have a numeric value, instead of this, there's a word "big" which means they have big invoicing.

In order to analyze this, I'd like to read all the rows and if a rows contains a non numeric value, read the previous numeric value it was found.

Easy example :

Company	Billing
A	10000
B	9500
C	Big
D	Big
E	Big
F	9000
G	Big

And I'd like to get :

Company	Billing
A	10000
B	9500
C	9500
D	9500
E	9500
F	9000
G	9000

How can I get this?

Regards, Marcel.

[nsetty · Accepted solution]

DataTmp:

Load * Inline

[

Company,Billing

A, 10000

B, 9500

C, Big

D, Big

E, Big

F, 9000

G, Big

];

FinalTbl:

LOAD

        [Company],

    Billing,

     If(trim(Billing)='Big', Peek('NewValue'), Billing) as NewValue

   Resident DataTmp;

drop table DataTmp;

[vishsaggi]

Nagesh you can also use this If condition in your Inline Load too like

DataTmp:

Load *, If(trim(Billing)='Big', Peek('NewBilling'), Billing) as NewBilling

Inline

[

Company,Billing

A, 10000

B, 9500

C, Big

D, Big

E, Big

F, 9000

G, Big

];

[tulabandula]

Peek function would resolve your scenario

peek( 'field', 0, 'Tab1' )

It gives based on your optional parameter to get the previous record to read which is equivalent to previous(field).

[marcel_olmo]

Thanks guys! Thay was it. Thanks for all your suggestions.

Regards, Marcel.