Removing zero values in the backend

ritvik4BI — Tue, 16 Mar 2021 17:21:46 GMT

Hi All,

I have been working on this scenario wherein the task is to flow previous month Sales value if the Sales value for the current month is zero which seems simple but complications arise when maybe lets say some Sales values are shown as "-" or when lets say for any particular month for example we have a Sales value which shows 0 for April and in the next row also has a particular value for April.

It will be more clear when I will take you step by step and finally we will come to the point where I am stuck. Note: I am loading a qvd file in which only two fields are relevant for the transformation but I need all the fields later on for my Visualization.

Step1: I load the QVD and derive the field MonthYear which is critical here. The code is as follows:

Table1:
LOAD*,
Date(Date1,'DD/MM/YYYY') as Date,
MonthName(Date1) as MonthYear
FROM
Hello.qvd
(qvd) WHERE Product_Group = 'xyz' and Year(Date1) = '2020' ;

If at this point I draw a table box with fields MonthYear and Sales It will look like:

MonthYear Sales
Jan 2020 2000
Feb 2020 1500
Mar 2020 0
Mar 2020 3200
Apr 2020 1700
Apr 2020 -
May 2020 5500
Jun 2020 -
Jul 2020 600
Aug 2020 2900
Sep 2020 1200
Oct 2020 4700
Nov 2020 550
Dec 2020 7500

Step2: For all the Sales values where the we have the value = "-", I remove it and replace with 0. The code now looks like:

Table2:
Load*,
If(IsNull([Sales]) or [Sales]='',0,[Sales]) as Sales1
Resident Table1 order by Date;
Drop Table Table1;

MonthYear Sales1
Jan 2020 2000
Feb 2020 1500
Mar 2020 0
Mar 2020 3200
Apr 2020 0
Apr 2020 1700
May 2020 5500
Jun 2020 0
Jul 2020 600
Aug 2020 2900
Sep 2020 1200
Oct 2020 4700
Nov 2020 550
Dec 2020 7500

Step3: Notice that June 2020 Sales1 value is still zero. Now according to my requirement if zero then it should be replaced by May 2020 i.e. 5500. So I write the following script:

Table3:
LOAD*,
if(Sales1=0,PREVIOUS (Sales1),Sales1) as Sales2
resident Table2;
Drop Table Table2;

The table box now looks like:

MonthYear Sales2
Jan 2020 2000
Feb 2020 1500
Mar 2020 0
Mar 2020 3200
Apr 2020 0
Apr 2020 1700
May 2020 5500
Jun 2020 0
Jun 2020 5500
Jul 2020 600
Aug 2020 2900
Sep 2020 1200
Oct 2020 4700
Nov 2020 550
Dec 2020 7500

Now as you can see March, April and June still have records for Sales=0 and below them are the desired Sales Values.

Now I want to delete the those records still showing 0 Sales. How to achieve this?

Also is there a better way for doing all that I've done above...

Regards.

Re: Removing zero values in the backend

QFabian — Wed, 17 Mar 2021 02:32:18 GMT

Hi @ritvik4BI , your script was almost ready, please check this script :

//the key is the function peek

Table2:
Load
MonthYear,
Sales,
if(isnull(Sales) or Sales = 0 or Sales='', peek(Sales2), Sales) as Sales2
Resident Table1;

drop table Table1; //Optional

topic Removing zero values in the backend in QlikView

Removing zero values in the backend

Re: Removing zero values in the backend