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 - 16 of 16 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

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-
out.Id::in.Id
out.col1::sum(in.col1)
out.col2::sum(in.col2)

  Was this answer useful?  Yes

Shri

  • Feb 28th, 2020
 

out :: rollup(in) =
begin
out.name :: in.name;
out.marks1::max(in.marks1);
out.marks2::max(in.marks2);
end;

  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.id = in.id;
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::rollup(in)=
begin
out.* :: in.*;
out.col1 :: max(in.col1);
out.col2 :: max(in.col2);
end;
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

Shikha

  • Jun 23rd, 2020
 

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

  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