Previus value in table

[#1]

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.

Please advise.

Thanks

[#2]

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!

[#3]

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.

there is any way to work around the problem ?

[#4]

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.

[#5]

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:

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

There is any way to work around ?

Thanks

[whiteline]

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.

[#7]

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]

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

[#9]

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 🙂

[#10]

Yes all sorted - thanks for help