To get only Not NUll Values

Suppose I have a scenario like below:
Input:
======
Id Col1 Col2
a 100 Null
a Null 200
b 300 Null
b Null 400
And I need output as:
Output:
=======
Id Col1 Col2
a 100 200
b 300 400
How to get only Not NUll Values?

Showing Answers 1 - 10 of 10 Answers

subhasis nag

  • Feb 10th, 2017
 

using first_defined function. it will take out only not null values

  Was this answer useful?  Yes

subhasis nag

  • Feb 13th, 2017
 

Using FBE giving the condition !in_null() will pass the not null value only

  Was this answer useful?  Yes

Rishiraj

  • Feb 13th, 2017
 

With rollup select key as {Id} and in transform do following:
string_filter_out(concatenation(in.Col1),"NULL").
Same for Col2.

  Was this answer useful?  Yes

Naveen

  • Feb 22nd, 2017
 

Hi Rishiraj,

The code which posted will not work......
However the approach is correct to achieve the output rather than using the string_filter_out we need to use string_filter like below....

/*Do computation*/
out::rollup(in)=
begin
out.* :: in.*;
out.col1 :: string_filter(concatenation(in.col1),0123456789);
out.col2 :: string_filter(concatenation(in.col2),0123456789);
end;

  Was this answer useful?  Yes

Anirban

  • May 31st, 2017
 

Please use the below code:
I/p file -> rolup (in rollup we can use accumulation to create vector) -> reformat (use vector_sort_dedup_first) -> normalize

  Was this answer useful?  Yes

geet

  • Jul 29th, 2017
 

Use roll up with Id as the key, and roll up function as below,
out.col1:: concatenation(in.col1, !is_null(in.col1));
out.col2 :: concatenation(in.col2, !is_null(in.col2)):

  Was this answer useful?  Yes

Bhanu

  • Nov 7th, 2017
 

/*Do computation*/
out::rollup(in)=
begin
out.* :: in.*;
out.amount:: concatenation(string_filter(in.amount,"0123456789"));
out.f:: concatenation(string_filter(in.f,"0123456789"));
end;

  Was this answer useful?  Yes

Sanjay Botcha

  • Nov 26th, 2018
 

input --reformat -- rollup -- output
Reformat :
out.id = in.id;
out.col1 :: if(is_null(in.col1))0 else in.col1;
out.col2 :: if(is_null(in.col2))0 else in.col2;
Rollup:
Summarize on key as id

  Was this answer useful?  Yes

M Farhan

  • Dec 29th, 2018
 

Sort. By Id col1 col2
Then dedup keep last !

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions