topic Re: Script to Obtain Previous Value for Null Value in QlikView

Script to Obtain Previous Value for Null Value

vignesh_s — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Script to Obtain Previous Value for Null Value

antoniotiman — Mon, 20 Nov 2017 17:24:05 GMT

Try to replace

Pick(Value1) with Pick(Value)

Re: Script to Obtain Previous Value for Null Value

trdandamudi — Mon, 20 Nov 2017 18:37:43 GMT

May be as below:

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

drop table temp1;

Re: Script to Obtain Previous Value for Null Value

vignesh_s — Tue, 21 Nov 2017 05:46:31 GMT

thanks for rply @Thirumala

i tried ur script but the result is wrng

Re: Script to Obtain Previous Value for Null Value

PrashantSangle — Tue, 21 Nov 2017 06:05:47 GMT

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,

Re: Script to Obtain Previous Value for Null Value

tresB — Tue, 21 Nov 2017 06:21:05 GMT

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.

Re: Script to Obtain Previous Value for Null Value

sumanta12 — Tue, 21 Nov 2017 07:49:40 GMT

Hi,

Use Previous function:

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

Re: Script to Obtain Previous Value for Null Value

vignesh_s — Wed, 22 Nov 2017 10:14:50 GMT

thanks its wrikng

Re: Script to Obtain Previous Value for Null Value

vignesh_s — Wed, 22 Nov 2017 10:16:49 GMT

thanksits working

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

Re: Script to Obtain Previous Value for Null Value

trdandamudi — Wed, 22 Nov 2017 13:08:03 GMT

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

Re: Script to Obtain Previous Value for Null Value

vignesh_s — Mon, 27 Nov 2017 07:26:55 GMT

Thanks