topic Re: Remove Null Values in QlikView

Remove Null Values

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have built the following report

Sales Order CSR	Sales Order Num	Sales Required Date	Sales Order Date	Item Code	Item Description	Sales Ordered Quantity	Sales Outstanding Quantity	=[Sales Order Status]='Authorised'	Sales Order Status
								0
	139900	07/10/2016	19/09/2016	F038333/1	item1	350000	0	-1	Authorised
	139900	07/10/2016	19/09/2016	S510-55	item2	350000	0	-1	Authorised
	139902	20/09/2016	20/09/2016	F009280	item3	48000	0	-1	Authorised
	139902	20/09/2016	20/09/2016	S533-65	item4	48000	0	-1	Authorised
AE	134974	29/05/2015	15/05/2015	F013908	item5	1000	1000	-1	Authorised
AE	136610	20/01/2016	29/10/2015	F039616	item6	100000	100000	-1	Authorised
AE	136610	20/01/2016	29/10/2015	S530-65	item7	100000	100000	-1	Authorised
AE	137214	22/01/2016	21/12/2015	F014057	item8	2000	2000	-1	Authorised
AE	137215	23/12/2015	21/12/2015	F014066	item9	1000	1000	-1	Authorised
AE	137370	01/02/2016	14/01/2016	F039817	item10	2000	600	-1	Authorised

I have 4 records were the Sales Order CSR is blank. Can you please tell me how I can dynamically remove these rows.

Thanks in advance

Dave

Re: Remove Null Values

sunny_talwar — Wed, 12 Oct 2016 10:44:44 GMT

Have you tried checking 'Suppress When Value Is Null' on the dimension tab for Sales Order CSR dimension?

Re: Remove Null Values

vardhancse — Wed, 12 Oct 2016 10:45:41 GMT

here is the sample script:

Load *

from Table1 where len(trim(Sales Order CSR))>0;

Re: Remove Null Values

sunny_talwar — Wed, 12 Oct 2016 10:46:30 GMT

If that doesn't work, may be you need to change your dimension into a calculated dimension or make some modification to the field in script using this to make Sales Order CSR blanks/empty into true nulls

If(Len(Trim([Sales Order CSR])) > 0, [Sales Order CSR])

Re: Remove Null Values

Anil_Babu_Samineni — Wed, 12 Oct 2016 11:18:26 GMT

Try Like this

Table:

Load * From Table;

[Final Table]:

Load * Resident Table where Len([Sales Order CSR]) <> Null()

Group By Sales Order CSR;

Then Crease Straight table with the Dim as Sales Order CSR and Metrics

Then For that Dim Sales Order CSR You might get down of that Suppress when null is value. You might need to check that. Then tell me still you have the problem after all responses

Re: Remove Null Values

Wed, 12 Oct 2016 11:22:19 GMT

Perfect thanks so much....you're brilliant