Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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 ?
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.
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
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.
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...
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
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 🙂
Yes all sorted - thanks for help