topic Re: How to replace null values with another value in QlikView

How to replace null values with another value

saurabh5 — Wed, 17 Jun 2015 00:44:23 GMT

hi all,

i am trying to replace null values by a value in RAU field as 'Unknown', by creating a new modified field apart from existing RAU field,after doing a left join.

below is the sample script i have added,

ASA:

LOAD * INLINE [
ID, filename
1, Test1
2, Test2
3, Test4
4, Test5
5, Test6
6, Test7
7, Test8
8, Test9
9, Test10
10, Test11
];

left join
CSA:
LOAD * INLINE [
ID, RAU
1, alpha
2, beta
3, gamma
4, delta
];

the output i am looking for is

id RAU_modified

1 alpa

2 beta
3 gamma

4 delta

5 Unknown

6 Unknown

7 Unknown

8 Unknown

9 Unknown

10 Unknown

Thanks for the help !!

Re: How to replace null values with another value

sunny_talwar — Wed, 17 Jun 2015 00:50:50 GMT

Try this script:

ASA:

LOAD * INLINE [

ID, filename

1, Test1

2, Test2

3, Test4

4, Test5

5, Test6

6, Test7

7, Test8

8, Test9

9, Test10

10, Test11

];

Left Join (ASA)

CSA:

LOAD * INLINE [

ID, RAU

1, alpha

2, beta

3, gamma

4, delta

];

NewASA:

NoConcatenate

LOAD ID,

filename,

If(Len(Trim(RAU)) = 0, 'Unknown', RAU) as RAU

Resident ASA;

DROP Table ASA;

Output in a table box:

Re: How to replace null values with another value

saurabh5 — Wed, 17 Jun 2015 17:33:48 GMT

Thanks SunIndia the solution worked.

Re: How to replace null values with another value

sunny_talwar — Wed, 17 Jun 2015 17:39:39 GMT

No problem

I am glad it worked

Best,

Sunny

Re: How to replace null values with another value

Chanty4u — Thu, 18 Jun 2015 04:10:30 GMT

hope this helps u in some cases

Thank u

Suresh