topic Hiden null values in QlikView

Hiden null values

cvalarcon — Wed, 25 Nov 2020 16:16:04 GMT

Hi expert

I have a table with null data in a column , so I need a pivot table without this null values

Input table

Motivo de Rechazo	Total
ERRORES ENTIDAD	$ 10
ERRORES ENTIDAD	$ 10
ERRORES ENTIDAD	$ 10
ERRORES ENTIDAD	$ 10
ERRORES ENTIDAD	$ 10
ERRORES ENTIDAD	$ 10
ERRORES ENTIDAD	$ 10
CTA EMBARGADA	$ 20
DEFECTO FORMAL	$ 30
	$ 10
	$ 15
	$ 35

output table

Motivo de Rechazo	Total
ERRORES ENTIDAD	$ 70
CTA EMBARGADA	$20
DEFECTO FORMAL	$ 30

I`m working in qlilkview so I put in chart properties the selection of Supress when value is null but still appear the null value . Is there any formula in the Dimensions to hide this null values .

Thanks in advance

Re: Hiden null values

Vegar — Sat, 23 Mar 2019 19:26:01 GMT

Are you sure you got NULL values? If they are selectable then they are not NULLS.

If they are not NULLS then you could solve it in the script when loading the Dimension field.

If(Len(trim([Motivo de Rechazo]))>0, [Motivo de Rechazo], NULL()) as [Motivo de Rechazo]

If they are in fact NULL then you could add a SET into your Total expression.

Sum({< [Motivo de Rechazo] *= {"*"} >} [Total])

Re: Hiden null values

cvalarcon — Sun, 24 Mar 2019 02:21:51 GMT

Hi Vegar ,
thanks for your prompt response, now is ok but I had to add in script this sentences :

Map_null:
MAPPING LOAD
NULL(),
'Nulo'
AutoGenerate 1;

MAP [Motivo de Rechazo] using Map_null;

SALESDATA:
LOAD [Actual Delivery Date],
CustKey as CustomerID,
DateKey,
[Discount Amount],
[Invoice Date],
[Invoice Number],
if(Len([Motivo de Rechazo])=0,Null(),[Motivo de Rechazo]) AS [Motivo de Rechazo],

But now appear in row Nul the 'Nulo' partial ok , so the question now is
How can I delete o hide this 'Nulo' row in the final table in order to have a total sum ?
Thanks

Re: Hiden null values

Vegar — Sun, 24 Mar 2019 05:14:15 GMT

Try
Sum({< [Motivo de Rechazo] -= {'Nulo'} >} [Total])