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

[mdmukramali]

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:

a:

LOAD * Inline

[

A,B

1,

,4

2,3

6,2

]

;

LOAD * ,

{FUNCTION} (A,B) AS NewField

Resident a;

DROP Table a;

OUTPUT:

NewField

1

4

2

6

Thanks,

mukram

[Anonymous]

Try with Peek() and Previous() function

[Peter_Cammaert · Accepted solution]

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.

[shaileshyerram]

The function u are looking for is ALT()

[MK_QSL]

a:

LOAD

  *,

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

Inline

[

  A,B

  1,

  ,4

  2,3

  6,2

]

;

[Anonymous]

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

What If I am having multiple continous null values like?

A, B

1,

, 3

, 4

, 5

2,

7,

8,

like this...

[mdmukramali]

Dear Peter,

Thanks for the help.

yes it's Alt Function.

[asgardd2]

It's works with system variable "NULLINTERPRET":

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

[MayilVahanan]

HI

Try like this

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

[

A, B

1,

, 3

, 4

, 5

2,

7,

8,

];

[Peter_Cammaert]

Apparently, the output needs to be:

NewField

1

3

4

5

2

7

8

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.