Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Previus value in table

Hi

I got a table with three columns (Date, Code, Level).

Sometimes I getting null values in level column, when in level column is null I need replace to last value for specific code.

Example below:

Record 11/11/2011, K20 - is null the I need to get last value (sort by date) for K20 which is 150.

table.jpg

Please advise.

Thanks

10 Replies
Not applicable

Re: Previus value in table

In the loadscript you can check for null values, and apply an if statement. In pseudo-code: if value isNull (in my code: len()<1) THEN get previousvalue, else, the current value

code

source:

load *

inline [

Date,Code,Level

10/11/2011,k10,50

10/11/2011,k20,150

11/11/2011,k10,60

11/11/2011,k20,

12/11/2011,k10,80

12/11/2011,k20,200

]

;

NoConcatenate

destination:

load

Date,Code,Level,

if(len(Level)<1,previous(Level),Level) as previousColumn

Resident source

order by Code,Date;//note: the orderby (and sequence of orderby is important for which previous() value is picked...

drop table source;

See also attached for the example in a qvw.

Hope it helps!

Not applicable

Re: Previus value in table

Hi Roberto,

Thanks for your answer - is working fine but when you I type in k30 code first time and the the level will be null, previus() function will return previus value of level but I will expect null because there was no last value for k30.

123.jpg
there is any way to work around the problem ?

Not applicable

Re: Previus value in table

Sure you can workaround this problem. In the if-statement, you can check whether the previous value of the field Code equals the current value of Code. If not, then return NULL, else return the value you want.

The updated code will be like this:

load
  Date,

  Code,

  Level,
   if(len(Level)<1,

      //here the additional check...

      if(previous(Code)=Code,

   previous(Level),

      //return NULL if previous code does NOT have the same value as the current CODE

      NULL()

      ),Level)

          as previousColumn

Resident source
order by Code,Date;

Hope it helps.

Not applicable

Re: Previus value in table

Hi I found the problem with multiples rows.

code:

source:

load *

inline [

Date,Code,Level

11/11/2011,k10,

15/07/2011,k10,

20/02/2011,k10,

25/08/2011,k10,

09/09/2011,k10,

01/12/2011,k10,

06/04/2011,k10,

04/05/2011,k10,

09/10/2011,k10,20

10/01/2011,k10,10

]

;

NoConcatenate

destination:

load

Date,Code,Level,

if(len(Level)<1,previous(Level),Level) as previousColumn,

if(len(Level)<1,if(previous(Code)=Code,previous(Level),NULL()),Level) as previousColumn1

Resident source

order by Code,Date asc;

drop table source;

and output:

12.jpg

Looks like the script just "copy" previous record for first empty column.

There is any way to work around ?

Thanks

whiteline
Honored Contributor II

Re: Previus value in table

Use peek() instead of previous().

previous() works with source so it gives you blanks except the first row.

Don't forget, with peek() you should use field names from resulting table.

Not applicable

Re: Previus value in table

Whiteline, could you complete the example? I tried using peek() with this statement

if(len(Level)<1,peek(Level,-1,'destination'),Level) as previousPEEK

But no difference...

whiteline
Honored Contributor II

Re: Previus value in table

Just use corresponding field names:

if(len(Level)<1,peek(previousColumn),Level) as previousColumn,

if(len(Level)<1,if(previous(Code)=Code,peek(previousColumn1),NULL()),Level) as previousColumn1

Not applicable

Re: Previus value in table

Ah... I didn't realize that peek could refer to its own fieldname. Good to know!

@krzysiekk: is your problem solved with this info?

If not, let us know what your current outcome is, and your desired outcome. I think with this additional info the problem can be solved :-)

Not applicable

Re: Previus value in table

Yes all sorted - thanks for help

Community Browser