topic How to convert missing/null value to zero in QlikView

How to convert missing/null value to zero

farheenayesha — Fri, 17 May 2019 10:58:43 GMT

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

Re: How to convert missing/null value to zero

dplr-rn — Fri, 17 May 2019 12:59:24 GMT

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.

Re: How to convert missing/null value to zero

farheenayesha — Fri, 17 May 2019 13:52:20 GMT

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.

Re: How to convert missing/null value to zero

dplr-rn — Fri, 17 May 2019 13:59:06 GMT

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

Re: How to convert missing/null value to zero

farheenayesha — Fri, 17 May 2019 15:50:00 GMT

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.

Re: How to convert missing/null value to zero

dplr-rn — Fri, 17 May 2019 16:08:13 GMT

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