picking/removing null values from straight table

[#1]

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_num	Status	Date	Type
A666A	open	13/08/2015
B777B	closed	12/08/2015	Exit
B777B	closed	12/08/2015
C888C	open	14/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_num	Status	Date	Type
A666A	open	13/08/2015
B777B	closed	12/08/2015	Exit
C888C	open	14/08/2015

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

[#2]

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

;

[#3]

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]

may be like this?

LOAD distinct

     Cust_Num,

     Status,

     Date,

     Type

From table_source

[amit_saini]

Hi,

Try this as dimension:

=if (not isnull(Type),Cust_Num)

or

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

Thanks,
AS

[Anonymous]

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?

[#7]

yes i wnat to remove ony duplicate values with null

[amit_saini]

Yes buddy you are right , I thought this other way

In this case distinct will work.

Thanks,

AS

[amit_saini]

Check:

https://community.qlik.com/message/751402#751402

Thanks,

AS