Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Partner
Partner

How to repeat previous numeric value in loading script

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 :

 

CompanyBilling
A10000
B9500
CBig
DBig
EBig
F9000
G

Big

And I'd like to get :

 

CompanyBilling
A10000
B9500
C9500
D9500
E9500
F9000
G9000

How can I get this?

Regards, Marcel.

1 Solution

Accepted Solutions
Partner
Partner

Re: How to repeat previous numeric value in loading script

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;

4 Replies
Partner
Partner

Re: How to repeat previous numeric value in loading script

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
Esteemed Contributor III

Re: How to repeat previous numeric value in loading script

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

];

Partner
Partner

Re: How to repeat previous numeric value in loading script

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

Partner
Partner

Re: How to repeat previous numeric value in loading script

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

Regards, Marcel.