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.
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.
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.
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
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
Rep&Product AS RepProdKey
// create one with all possible combinations. how to do this depends on your env
Rep,Product, Rep&Product AS RepProdKeyTemp
//create a dummy rows for fact table for Rep Prod combination not in fact
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