topic Re: Previus value in table in QlikView

Previus value in table

Sat, 08 Sep 2012 20:28:26 GMT

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

Re: Previus value in table

Sat, 08 Sep 2012 20:46:04 GMT

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!

Re: Previus value in table

Sat, 08 Sep 2012 21:07:52 GMT

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 ?

Re: Previus value in table

Sat, 08 Sep 2012 22:07:26 GMT

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.

Re: Previus value in table

Mon, 10 Sep 2012 08:43:54 GMT

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

Re: Previus value in table

whiteline — Mon, 10 Sep 2012 08:49:00 GMT

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.

Re: Previus value in table

Mon, 10 Sep 2012 09:09:47 GMT

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...

Re: Previus value in table

whiteline — Mon, 10 Sep 2012 09:29:28 GMT

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

Re: Previus value in table

Mon, 10 Sep 2012 09:36:15 GMT

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 🙂

Re: Previus value in table

Mon, 10 Sep 2012 09:54:43 GMT

Yes all sorted - thanks for help

Re: Previus value in table

whiteline — Mon, 10 Sep 2012 11:12:20 GMT

Please mark the answer as correct to close the thread.