topic Re: How to repeat previous numeric value in loading script in QlikView

How to repeat previous numeric value in loading script

marcel_olmo — Thu, 06 Sep 2018 14:52:28 GMT

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.

Re: How to repeat previous numeric value in loading script

nsetty — Thu, 06 Sep 2018 15:10:08 GMT

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;

Re: How to repeat previous numeric value in loading script

vishsaggi — Thu, 06 Sep 2018 15:23:55 GMT

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

];

Re: How to repeat previous numeric value in loading script

tulabandula — Thu, 06 Sep 2018 15:25:47 GMT

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).

Re: How to repeat previous numeric value in loading script

marcel_olmo — Thu, 06 Sep 2018 15:42:40 GMT

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

Regards, Marcel.