topic Re: fill the value whre data is null in QlikView

fill the value whre data is null

Tue, 17 Sep 2013 11:20:51 GMT

Dear communty,

Mysenario will be

i have

item amount

item1 10000

item2 0

item3 0

i want to be fill 10000 where amount is 00

kindly guide me

Re: fill the value whre data is null

Tue, 17 Sep 2013 11:36:23 GMT

kindly use the below expression for dis[playing amount

if(amount='00' or amount ='Null' ,10000)

Thanks,

D J

Re: fill the value whre data is null

Tue, 17 Sep 2013 11:40:55 GMT

Thanks for Reply

but i am not expecting this way it should show the value auttomatically not manuvally

item2 and item 3 shold show item 1 value by dynamically not manuvally

in the future value may be change

Re: fill the value whre data is null

Gysbert_Wassenaar — Tue, 17 Sep 2013 11:43:50 GMT

Have a look at this document: Generating Missing Data In QlikView

Re: fill the value whre data is null

marcus_malinow — Tue, 17 Sep 2013 11:46:58 GMT

You could try using a peek.

For example:

LOAD

item,

if(isnull(Amount) or Amount = 0,

peek('Amount', -1),

Amount) as Amount

FROM

.....

ORDER BY Amount desc;

Re: fill the value whre data is null

sathishkumar_go — Tue, 17 Sep 2013 12:09:54 GMT

Hi, Please find the attached file. I hope the attached file will solve your problem. Regards Sathish

Re: fill the value whre data is null

Thu, 19 Sep 2013 09:46:26 GMT

Hi,

lease use the below function to load the data;

LOAD item,

If(Isnull(amount) or amount=0, peek('Newamount'),amount) as Newamount

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

Thanks,

D J

Re: fill the value whre data is null

dmohanty — Thu, 19 Sep 2013 09:51:15 GMT

This will help you:

If(Isnull(amount) or amount= '0', peek(NewAmount),amount) as NewAmount

Re: fill the value whre data is null

Thu, 19 Sep 2013 10:18:22 GMT

see below result

item	amount	Replace with previous value
	11008	11008
item1	10000	10000
item2	0	10000
item3	100	100
item4	0	100
i4	0	0
i5	200	200
i6	700	700
i7	8	8
i8	0	8

use this cond in ur expression

if(amount=0,Above(amount,1),amount)

then u get this result

above table

1st exp is

amount

2nd exp is

if(amount=0,Above(amount,1),amount)