Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Diego_780
Creator
Creator

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

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:

DiegoF_0-1712128987149.png

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

DiegoF_1-1712129075027.png

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!

 

 

Labels (4)
1 Solution

Accepted Solutions
MayilVahanan

Hi

Try like 

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

4 Replies
MayilVahanan

Hi

Try like 

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Diego_780
Creator
Creator
Author

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
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;