To get only Not NUll Values

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

Showing Answers 1 - 17 of 17 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


  • Feb 13th, 2017

With rollup select key as {Id} and in transform do following:
Same for Col2.

  Was this answer useful?  Yes


  • 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.* :: in.*;
out.col1 :: string_filter(concatenation(in.col1),0123456789);
out.col2 :: string_filter(concatenation(in.col2),0123456789);

  Was this answer useful?  Yes


  • 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


  • 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


  • Nov 7th, 2017

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

  Was this answer useful?  Yes

Sanjay Botcha

  • Nov 26th, 2018

input --reformat -- rollup -- output
Reformat : =;
out.col1 :: if(is_null(in.col1))0 else in.col1;
out.col2 :: if(is_null(in.col2))0 else in.col2;
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

Srushti Makhija

  • Jun 13th, 2019

Use first function in rollup

  Was this answer useful?  Yes

Amaan Ansari Exusia

  • Aug 21st, 2019

Input-> Sort (key : id) -> Roll up (ID KEY) (Concat : col1 concat col2) -> Output

  Was this answer useful?  Yes

Shubham Gautam

  • Sep 16th, 2019

use rollup on Id column>>> in the rollup transform do-

  Was this answer useful?  Yes


  • Feb 28th, 2020

out :: rollup(in) =
begin ::;

  Was this answer useful?  Yes

Bibhu Prasad Das

  • Mar 27th, 2020

Easiest way is to replace the "Null" to a finite value i.e zero preferably and then rollup the data based on id as key to find max value of the fields to get the result easily. Please read whole file in string instead of using decimal datatype for second and third field.
Flow :-
input --> reformat --> sort --> rollup --> output
Reformat : -> replace null with Zero =;
out.col1 :: if((in.col1=="Null")"0" else in.col1;
out.col2 :: if((in.col2=="Null")"0" else in.col2;
sort : -> id
Rollup: -> use id as key
/*Summarize on key as id*/
/*Do computation*/
out.* :: in.*;
out.col1 :: max(in.col1);
out.col2 :: max(in.col2);
P.S - You cant operate on NULL in rollup unless you handle them prior to rollup. Here its only a string "Null" and not NULL value. So please give substantial thought on coming up with answer.
Additionally i have tried above stuff and it gave me proper result.

  Was this answer useful?  Yes


  • Jun 23rd, 2020

Use Input file --> Sort(key - id) --> Rollup (key -id) --> Output file
In Rollup :-
out :: rollup(in)=
begin ::;
out.col1 :: concatenation(in.col1);
out.col2:: concatenation(in.col2);

  Was this answer useful?  Yes

Sonal Singh

  • Aug 8th, 2020

You can directly FBX and add !is_null(field1) or !is_null(field2)

  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