topic Re: How to convert null/missing value to zero and rank the zero values with the same rank in QlikView

How to convert null/missing value to zero and rank the zero values with the same rank

farheenayesha — Thu, 16 May 2019 17:36:55 GMT

Hi,

Please help me to convert the blank values to zero and rank them. Please find the data below. I want to replace the blanks with zero and rank them to 20.

Expressions:

Sales $: Sum(Sales)

Base $: sum(Base)

+/-Base: Sum(Sales)-sum(Base)

National Rank: Rank(Sum(Sales)-sum(Base))

Rep Name	Sales $	Base $	+/- Base $	National Rank
Rep Name 1	20728	8718	$12,010	1
Rep Name 2	11760	12888	($1,128)	9
Rep Name 3	16422	11649	$4,773	2
Rep Name 4	-	-	-	-
Rep Name 5	6098	5027	$1,071	7
Rep Name 6	-	-	-	-
Rep Name 7	7780	5197	$2,583	4
Rep Name 8	0	2172	($2,172)	12
Rep Name 9	5757	7449	($1,692)	10
Rep Name 10	0	2913	($2,913)	14
Rep Name 11	0	7283	($7,283)	18
Rep Name 12	11473	16071	($4,598)	17
Rep Name 13	-	-	-	-
Rep Name 14	3691	14444	($10,753)	19
Rep Name 15	6447	8494	($2,047)	11
Rep Name 16	2546	1328	$1,218	6
Rep Name 17	5234	7622	($2,388)	13
Rep Name 18	0	3990	($3,990)	15
Rep Name 19	-	-	-	-
Rep Name 20	3059	1585	$1,474	5
Rep Name 21	19503	18883	$620	8
Rep Name 22	18751	14777	$3,974	3
Rep Name 23	0	4013	($4,013)	16

I want the result as below table

Rep Name	Sales $	Base $	+/- Base $	National Rank
Rep Name 1	20728	8718	$12,010	1
Rep Name 2	11760	12888	($1,128)	9
Rep Name 3	16422	11649	$4,773	2
Rep Name 4	0	0	0	20
Rep Name 5	6098	5027	$1,071	7
Rep Name 6	0	0	0	20
Rep Name 7	7780	5197	$2,583	4
Rep Name 8	0	2172	($2,172)	12
Rep Name 9	5757	7449	($1,692)	10
Rep Name 10	0	2913	($2,913)	14
Rep Name 11	0	7283	($7,283)	18
Rep Name 12	11473	16071	($4,598)	17
Rep Name 13	0	0	0	20
Rep Name 14	3691	14444	($10,753)	19
Rep Name 15	6447	8494	($2,047)	11
Rep Name 16	2546	1328	$1,218	6
Rep Name 17	5234	7622	($2,388)	13
Rep Name 18	0	3990	($3,990)	15
Rep Name 19	0	0	0	20
Rep Name 20	3059	1585	$1,474	5
Rep Name 21	19503	18883	$620	8
Rep Name 22	18751	14777	$3,974	3
Rep Name 23	0	4013	($4,013)	16

@sunny_talwar @dplr-rn

Re: How to convert null/missing value to zero and rank the zero values with the same rank

dplr-rn — Thu, 16 May 2019 17:43:34 GMT

When you load the data try below options
if(len(Sales)=0,0,Sales) as Sales
or Alt(Sales,0) as Sales

Re: How to convert null/missing value to zero and rank the zero values with the same rank

farheenayesha — Thu, 16 May 2019 17:51:49 GMT

Hi Dilip,
Thanks for the reply. I tried both, but none of them is working 😞

Re: How to convert null/missing value to zero and rank the zero values with the same rank

dplr-rn — Thu, 16 May 2019 17:53:52 GMT

is it coming from your source as space? try below new column and check the output
len(Sales) as len

Re: How to convert null/missing value to zero and rank the zero values with the same rank

farheenayesha — Thu, 16 May 2019 18:49:58 GMT

Hi Dilip,
I tried the len as above. However, the below four Reps (Rep 4, 6, 13, 19 ) are not appearing in the table when i use Len. I feel the value is missing in the data for these four reps. Any idea how to convert the missing data to zero?

Rep Name	Sales $	Base $	+/- Base $	National Rank
Rep Name 1	20728	8718	$12,010	1
Rep Name 2	11760	12888	($1,128)	9
Rep Name 3	16422	11649	$4,773	2
Rep Name 4	0	0	0	20
Rep Name 5	6098	5027	$1,071	7
Rep Name 6	0	0	0	20
Rep Name 7	7780	5197	$2,583	4
Rep Name 8	0	2172	($2,172)	12
Rep Name 9	5757	7449	($1,692)	10
Rep Name 10	0	2913	($2,913)	14
Rep Name 11	0	7283	($7,283)	18
Rep Name 12	11473	16071	($4,598)	17
Rep Name 13	0	0	0	20
Rep Name 14	3691	14444	($10,753)	19
Rep Name 15	6447	8494	($2,047)	11
Rep Name 16	2546	1328	$1,218	6
Rep Name 17	5234	7622	($2,388)	13
Rep Name 18	0	3990	($3,990)	15
Rep Name 19	0	0	0	20
Rep Name 20	3059	1585	$1,474	5
Rep Name 21	19503	18883	$620	8
Rep Name 22	18751	14777	$3,974	3
Rep Name 23	0	4013	($4,013)	16

Re: How to convert null/missing value to zero and rank the zero values with the same rank

rick_hoorn — Mon, 17 Jun 2019 11:36:11 GMT

Use the ALT function. I a NULL value appears, it will be replaced with the value 0

Example:

ALT([Sales,0) will com up with 0 when null value