How to convert missing/null value to zero

[farheenayesha]

Hi,

I have 2 Products, 1 Align (group)  and 23 Reps who Sells these products. 

Product 1 is  Sold by  19 Reps 

Product 2 is sold by 20 Reps

The Products which are not Sold by Rep1 (but sold by Rep 2)  appears as '-' and i want to convert these '-' to Zero and Rank them as the last value.

Example:

In the below Pic, Rep4, Rep 6 and Rep 13 dose not sell Product 1 but sells Product 2, hence for these Reps it is showing as '-' . Now i want to convert these '-' to zero and Rank them as last value (i,e. 20). Please help me to resolve. Attaching dummy data and app.

I want to get the result as below for Product1

@sunny_talwar @dplr-rn 

[dplr-rn]

i see that the data is missing for such entries.

e.g. rep 4 does not have Product 1.

Are you using sense or view

if it was qlikview there is a property to set null value for the table. Chart properties --> Presentation set null symbol

sense I believe you may need to have some dummy data there. 

[farheenayesha]

Hi Dilip,

               Thanks for the reply. Yes Rep 4 doesnt have data. I am using qliksense and i have attached the qvf file as well. There is no dummy data for that Rep4 for Prodcut1. Could you please let me know, if there is any way to add dummy data through Qliksense because i cannot add in the database.

[dplr-rn]

step 1 Create a table of distinct rep, product combination.
step 2 create a concatented key rep&product in you main table and the new table
step 3 load new table again with a not in on the concatenated key , add sale and other columns with 0 value

[farheenayesha]

Hi Dilip,
Thanks for the suggestion. I understood the first two points but not so clear with the third one.
It would be great if you can elaborate.

[dplr-rn]

See below dummy code explaining the theory . hope its clear enough to nudge you in right direction

//Load temp table to get combination in current data
TempKeys:
load distinct
Rep&Product AS RepProdKey
resident fact;
// create one with all possible combinations. how to do this depends on your env
TempProdRep:
LOAD
Rep,Product, Rep&Product AS RepProdKeyTemp
FROM SOURCE; 


Concatenate (Fact)
//create a dummy rows for fact table for Rep Prod combination not in fact
load
Rep
,Product
RepProdKeyTemp as RepProdKey
, 0 as Sales
,0 as Base
//.. add other columns with dummy values
resident TempProdRep where not exists (RepProdKeyTemp ,RepProdKey) 
;
drop temp tables