topic Re: Fill empty field with the value from previous cell in QlikView

Fill empty field with the value from previous cell

Mon, 31 Oct 2016 18:02:48 GMT

Hello Community,

I have created a table that contains Period (Year and Month from 2010 till today).

For each period I have Project #, Project Description, Project type, Fund Type and PO # (can be multiple PO's for one project).

The From Another table I join Ending balance for each Project. As ending balance is a calculation of beginning balance and PO amount the record will be added only on date when the PO has been created (Please see image below)

So what I would like to do is to populate all the fields that are below the ending balance with the ending balance amount until the next populated filed.

I am trying the following code but it only populate the one next row after the balance amount

load *, if(Len(Trim(Ending_Balance)) = 0,peek(Ending_Balance),Ending_Balancet) as PREnding_Balance

resident BalanceReport2

order by POnum, ProjectID,ProjectDesc,ProjectType,FundType,Periods;

drop table BalanceReport2;

Pleas help...

Re: Fill empty field with the value from previous cell

maxgro — Tue, 01 Nov 2016 07:22:41 GMT

if(len(trim(Ending_Balance)), Ending_Balance, Peek('PREnding_Balance')) as PREnding_Balance

this is from Qlik help

peek(fieldname [ , row [ , tablename ] ] )

Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Re: Fill empty field with the value from previous cell

Tue, 01 Nov 2016 13:16:28 GMT

Thanks Massimo.

That works.