topic Re: Select the second field value if the first field value is null in QlikView

Select the second field value if the first field value is null

mdmukramali — Thu, 24 Dec 2015 08:15:07 GMT

Dear ,

i want to create a new field which contains A values, if any place A value is missing (Null) then it should select the value from second B field.

i don't remember the function for it.

example:

LOAD * Inline

[

A,B

2,3

6,2

]

;

LOAD * ,

{FUNCTION} (A,B) AS NewField

Resident a;

DROP Table a;

OUTPUT:

NewField

Thanks,

mukram

Re: Select the second field value if the first field value is null

Anonymous — Thu, 24 Dec 2015 08:22:48 GMT

Try with Peek() and Previous() function

Re: Select the second field value if the first field value is null

Peter_Cammaert — Thu, 24 Dec 2015 08:28:52 GMT

Replace {FUNCTION} with ALT. The Alt() function will choose the first non-NULL value from a list of parameters. Alt(A,B) will select A, or B if A contains a NULL (or non-numeric) value.

Re: Select the second field value if the first field value is null

shaileshyerram — Thu, 24 Dec 2015 08:30:37 GMT

The function u are looking for is ALT()

Re: Select the second field value if the first field value is null

MK_QSL — Thu, 24 Dec 2015 08:31:05 GMT

LOAD

If(IsNull(A) or Len(Trim(A))=0, B, A) as NewField

Inline

[

A,B

2,3

6,2

]

;

Re: Select the second field value if the first field value is null

Anonymous — Thu, 24 Dec 2015 08:32:17 GMT

Okay, so ALT() will pick alternate values..

What If I am having multiple continous null values like?

A, B

, 3

, 4

, 5

like this...

Re: Select the second field value if the first field value is null

mdmukramali — Thu, 24 Dec 2015 08:34:29 GMT

Dear Peter,

Thanks for the help.

yes it's Alt Function.

Re: Select the second field value if the first field value is null

asgardd2 — Thu, 24 Dec 2015 08:34:30 GMT

It's works with system variable "NULLINTERPRET":

SET NULLINTERPRET =<null>;
a:
LOAD * Inline
[A,B
1,<null>
<null>,4
2,3
6,2
];

Re: Select the second field value if the first field value is null

MayilVahanan — Thu, 24 Dec 2015 08:35:40 GMT

Try like this

LOAD *, If(Len(Trim(A))=0, B, A) as C Inline

[

A, B

, 3

, 4

, 5

];

Re: Select the second field value if the first field value is null

Peter_Cammaert — Thu, 24 Dec 2015 08:37:57 GMT

Apparently, the output needs to be:

NewField

The only restriction Mohammed should take into account is that for every missing A-value, there should be a B-value, or he'll still end up with NULLs in field NewField.

Re: Select the second field value if the first field value is null

Anonymous — Thu, 24 Dec 2015 08:44:47 GMT

Got it