Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
vignesh_s
Contributor

Script to Obtain Previous Value for Null Value

hi everyone, i tried to obtain previous value for null values and wrote the script as shown in screenshot, but the result i got was not correct, can any please give the solution for this

2.png,Untitled.png

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Script to Obtain Previous Value for Null Value

Try to replace

Pick(Value1) with Pick(Value)

View solution in original post

10 Replies
antoniotiman
Honored Contributor III

Re: Script to Obtain Previous Value for Null Value

Try to replace

Pick(Value1) with Pick(Value)

View solution in original post

trdandamudi
Honored Contributor II

Re: Script to Obtain Previous Value for Null Value

May be as below:

Load Date1,
Properties1,
If (Len(Trim(Value1))=0, Peek('Value'),Value1) as Value
Resident temp1;

drop table temp1;

vignesh_s
Contributor

Re: Script to Obtain Previous Value for Null Value

thanks for rply @Thirumala

i tried ur script but the result is wrng

3.png4.png

Re: Script to Obtain Previous Value for Null Value

Hi,

Logic suggested by trdandamudi‌ must work.

Data:

LOAD * INLINE [

    Date1, Properties, Value1

    11/12/12, A/c, 40

    12/12/12, A/c,

    13/12/12, A/c, 50

    14/12/12, Fridge, 100

    15/12/12, Fridge,

    16/12/12, Fridge,

    17/12/12, Fridge, 250

    15/12/12, TV, 350

    16/12/12, TV,

    27/12/12, TV,

];

NoConcatenate

final_Data:

Load Date1 as Date,

Properties as Property,

if(len(trim(Value1))=0,Peek('Value'),Value1) as Value

Resident Data;

DROP Table Data;

EXIT SCRIPT;

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
MVP
MVP

Re: Script to Obtain Previous Value for Null Value

In the screen shot, you are probably taking Value1 instead of Value in the table box. I.e. you would see the right values in Value field. Also note, in table box you would not see all the values. You should look at the table view (Ctrl+T) instead to check if it is working fine.

Highlighted
sumanta12
Contributor II

Re: Script to Obtain Previous Value for Null Value

Hi,

Use Previous function:

IF(LEN(VALUE)=0,PREVIOUS(VALUE),VALUE) AS NEW_VALUE

vignesh_s
Contributor

Re: Script to Obtain Previous Value for Null Value

thanks its wrikng

vignesh_s
Contributor

Re: Script to Obtain Previous Value for Null Value

thanksits working

if i use isNULL(Value) instead of len(value)=0 ,im nt geting the out put,canu explain why

trdandamudi
Honored Contributor II

Re: Script to Obtain Previous Value for Null Value

You are welcome...

Might be you don't have nulls and you have only blanks and that is the reason you are not getting the output. Nulls  are different from blanks. Give a try on the below and let me know:

If(IsNull(Value) OR Value='',Peek('Value'),Value1) as Value