topic Re: picking/removing null values from straight table in QlikView

picking/removing null values from straight table

Thu, 21 Jan 2016 14:30:15 GMT

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.

Re: picking/removing null values from straight table

Thu, 21 Jan 2016 18:37:04 GMT

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

;

Re: picking/removing null values from straight table

Fri, 22 Jan 2016 05:30:57 GMT

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

Re: picking/removing null values from straight table

Anonymous — Fri, 22 Jan 2016 05:38:55 GMT

may be like this?

LOAD distinct

Cust_Num,

Status,

Date,

Type

From table_source

Re: picking/removing null values from straight table

amit_saini — Fri, 22 Jan 2016 05:40:39 GMT

Hi,

Try this as dimension:

=if (not isnull(Type),Cust_Num)

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

Thanks,
AS

Re: picking/removing null values from straight table

Anonymous — Fri, 22 Jan 2016 05:44:57 GMT

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?

Re: picking/removing null values from straight table

Fri, 22 Jan 2016 05:49:00 GMT

yes i wnat to remove ony duplicate values with null

Re: picking/removing null values from straight table

amit_saini — Fri, 22 Jan 2016 05:50:22 GMT

Yes buddy you are right , I thought this other way

In this case distinct will work.

Thanks,

Re: picking/removing null values from straight table

amit_saini — Fri, 22 Jan 2016 05:51:25 GMT

Check:

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

Thanks,