Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

picking/removing null values from straight table

Hi,

I have a case in a straight table,  where i need to remove duplicates which have null values in one field.

The straight table is as follows:

Cust_numStatusDateType
A666Aopen13/08/2015
B777Bclosed12/08/2015Exit
B777Bclosed12/08/2015
C888Copen14/08/2015

My requirement is such that, the resultant table should not have duplicates and it should only get the field which do not have 'Type' as NULL

Result should be as follows:

Cust_numStatusDateType
A666Aopen13/08/2015
B777Bclosed12/08/2015Exit
C888Copen14/08/2015

I would like to have some input in achieving this scenario.

8 Replies
Not applicable
Author

You can try with Group by clause:

LOAD

     Cust_Num,

     Status,

     Date,

     Maxstring(Type) AS Type

From table_source

Group by

     Cust_Num,

     Status,

     Date

;

Not applicable
Author

i'm trying to write an expression in the straight table,

in my original table i have duplicate rows, i want to remove the row in which Type = null

Anonymous
Not applicable
Author

may be like this?

LOAD distinct

     Cust_Num,

     Status,

     Date,

     Type

From table_source

amit_saini
Master III
Master III

Hi,

Try this as dimension:

=if (not isnull(Type),Cust_Num)

or

=if(len(Type) > 1, Cust_Num)

Thanks,
AS

Anonymous
Not applicable
Author

Amit,

If I am not wrong, it will remove all the null values of field

And he want to remove only duplicate value with null...

John, correct me if I am wrong?

Not applicable
Author

yes i wnat to remove ony duplicate values with null

amit_saini
Master III
Master III

Yes buddy you are right , I thought this other way

In this case distinct will work.

Thanks,

AS

amit_saini
Master III
Master III