Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
farheenayesha
Contributor

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

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 NameSales $Base $+/-  Base $National Rank
Rep Name 1207288718$12,0101
Rep Name 21176012888($1,128)9
Rep Name 31642211649$4,7732
Rep Name 4----
Rep Name 560985027$1,0717
Rep Name 6----
Rep Name 777805197$2,5834
Rep Name 802172($2,172)12
Rep Name 957577449($1,692)10
Rep Name 1002913($2,913)14
Rep Name 1107283($7,283)18
Rep Name 121147316071($4,598)17
Rep Name 13----
Rep Name 14369114444($10,753)19
Rep Name 1564478494($2,047)11
Rep Name 1625461328$1,2186
Rep Name 1752347622($2,388)13
Rep Name 1803990($3,990)15
Rep Name 19----
Rep Name 2030591585$1,4745
Rep Name 211950318883$6208
Rep Name 221875114777$3,9743
Rep Name 2304013($4,013)16

 

I want the result as below table

Rep NameSales $Base $+/-  Base $National Rank
Rep Name 1207288718$12,0101
Rep Name 21176012888($1,128)9
Rep Name 31642211649$4,7732
Rep Name 400020
Rep Name 560985027$1,0717
Rep Name 600020
Rep Name 777805197$2,5834
Rep Name 802172($2,172)12
Rep Name 957577449($1,692)10
Rep Name 1002913($2,913)14
Rep Name 1107283($7,283)18
Rep Name 121147316071($4,598)17
Rep Name 1300020
Rep Name 14369114444($10,753)19
Rep Name 1564478494($2,047)11
Rep Name 1625461328$1,2186
Rep Name 1752347622($2,388)13
Rep Name 1803990($3,990)15
Rep Name 1900020
Rep Name 2030591585$1,4745
Rep Name 211950318883$6208
Rep Name 221875114777$3,9743
Rep Name 2304013($4,013)16

 

@sunny_talwar @dilipranjith 

Labels (1)
5 Replies
Partner
Partner

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

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

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

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

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

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

farheenayesha
Contributor

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

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 NameSales $Base $+/-  Base $National Rank
Rep Name 1207288718$12,0101
Rep Name 21176012888($1,128)9
Rep Name 31642211649$4,7732
Rep Name 400020
Rep Name 560985027$1,0717
Rep Name 600020
Rep Name 777805197$2,5834
Rep Name 802172($2,172)12
Rep Name 957577449($1,692)10
Rep Name 1002913($2,913)14
Rep Name 1107283($7,283)18
Rep Name 121147316071($4,598)17
Rep Name 1300020
Rep Name 14369114444($10,753)19
Rep Name 1564478494($2,047)11
Rep Name 1625461328$1,2186
Rep Name 1752347622($2,388)13
Rep Name 1803990($3,990)15
Rep Name 1900020
Rep Name 2030591585$1,4745
Rep Name 211950318883$6208
Rep Name 221875114777$3,9743
Rep Name 2304013($4,013)16
rick_hoorn
New Contributor

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

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