topic Re: Replace null value with expression output in QlikView

Replace null value with expression output

Thu, 27 Feb 2014 09:24:05 GMT

My Scenario is

Group Amount

A 100

B 200

D 400

convert to

Group Amount

A 100

B 200

C 300 i.e (B+A)

D 400

E 500 i.e (D+A)

there can be more null values like E and C .

New sum (Amount ) is calculated after replacing null with values.

Re: Replace null value with expression output

alexandros17 — Thu, 27 Feb 2014 09:27:18 GMT

Try something like:

If(isnull(myField), Sum(previous(myField), myField)

Re: Replace null value with expression output

maxgro — Thu, 27 Feb 2014 20:20:11 GMT

SCRIPT

Source:

load * inline [

Group, Amount

A, 100

B, 200

D, 400

];

Final:

NoConcatenate load

Group,

// when Amount is null sum previuos Amount and Amount of first row (A)

if(len(trim(Amount))=0, peek(Amount) + peek(Amount, 0), Amount) as Amount

Resident Source

order by Group;

drop Table Source;

RESULT

Group	Amount
A	100
B	200
C	300
D	400
E	500

Re: Replace null value with expression output

Fri, 28 Feb 2014 06:49:13 GMT

Thanks For Reply.

But One more concern is that we are taking value by specifying index to peek() but I want the value to peek

by matching the group and logic to fill different null values are different.

Can i achieve this in front end using IF or may be by Set Analysis.

Re: Replace null value with expression output

maxgro — Sat, 01 Mar 2014 15:37:49 GMT

if the logic is different for every Group with missing Amount you may try in a chart ordered by Group with

DIMENSION Group

EXPRESSION

if(Group='C', concat(total aggr(if(Group='A', sum(Amount)), Group), '-', Group) + above(sum(Amount)),

if(Group='E', concat(total aggr(if(Group='A', sum(Amount)), Group), '-', Group) + above(sum(Amount)),

sum(Amount) ))