Use previous or peek to fill null value until find new value in a column

[DiegoF]

Hi Qlik professionals!

I´m having trouble with a table, because in the Excel, there is a column with a value that is combined, so it leaves null values to the next rows:

I need to fill the Sector column with the same sector until a new one arrives, i tried using Previous() like this:

I´ve searched similar problems and the formula is like this, but I can´t manage to replicate the same value until the new one, only for the next one to the one that is filled.

Any suggestions please?

Thanks in advance!

[MayilVahanan · Accepted solution]

Hi

Try like 

If(Isnull(A), Peek('Sector2'), A) as Sector2

[DiegoF]

How can I peek Sector2 if it´s not created yet? 

[MayilVahanan]

Hi

Please check the Peek function and example 3 

Peek - script function | QlikView Help

[marcus_sommer]

Isnull() won't work because your source is an Excel which hasn't a real NULL else only EMPTY values which could be queried like you did with len(trim()). The interrecord-functions and the if-query are also looking ok., whereby I'm not quite sure how the exclusion of labels in the file-format may impact the syntax or the results.

What happens by applying this (simplified):

load *, if(len(trim(Sector)), Sector, previous(Sector) as New;
load A as Sector, B, C from YourSource;