QV20 Filter the null values of column in set analysis

I have SCOTT.EMP table with us, and want to filter out null values .
the basic requirement is to filter the data using set analysis whose MGR is null.

Dimension - MGR
expression - SUM (SAL)

Now i want to remove null's from dimension of chart (i.e. sum= 5000 where MGR is null),
i can do in various ways but i want to use using "set analysis"


expression - sum({<MGR-={'=Len(Trim(MGR))=0'} >} [SAL])

QV19 Replace all null values

Null values in database can lead a big problem while calculating the dimensions.
In other word we can say that it is not good if database having null values in it.

It is good if we remove all null values. in Qlikview You can set a variable which will replace all null value with some string.

In very start of script/ Main tab of your document you need to add

NullAsValue *;
Set NullValue = 'NULL' ;

and then reload the document.

Below is the image will show data behaviour "COMM" column before and after adding variable.

QV18 Duplicate values in dimension

Duplicate values in dimensions:




produce issue:
create table region (area varchar2(30), district varchar2(30), city varchar2(30));
insert into region values ('chandigarh', 'chandigarh', 'chandigarh');
insert into region values ('punjab', 'patiala', 'patiala');
insert into region values ('punjab', 'patiala', 'nabha');
insert into region values ('punjab', 'patiala', 'SAN');
insert into region values ('punjab', 'SAS', 'SAS');
insert into region values ('punjab', 'SAS', 'ZRK');

i added all column area , district, and city in dimension of PIVOT table
in city i have repeated values same reside in DISTRICT

and these repeated/duplicate values cause a bad result in the end.

The city in red box is extra and some time leads to trouble depend upon the expression. In simple words if i say, i dont want to see the repeated city name "patiala" in city column.


Where patiala is act as city as well as district.

We can fix this in two ways:
- at script level
- at chart level

AT SCRIPT level

at script level my table look like this

After modification in script it look like this:

use below script for above approach:
REG:
LOAD AREA,
    DISTRICT,
    if(CITY = DISTRICT,null(),CITY) as CITY;
SQL SELECT *
FROM SCOTT.REGION;

AT CHART level

Use Go inside the Begin end in sqlserver

While i am working on one day having task of add column if not exists in the table, then add it and update it with some values

i used below scripts:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'EMP' AND COLUMN_NAME = 'SAL_COMM')
BEGIN

ALTER TABLE EMP ADD SAL_COMM int;
UPDATE EMP SET SAL_COMM = coalesce(SAL+COMM,sal,0);
END


Msg 207, Level 16, State 1, Line 6
Invalid column name 'SAL_COMM'.

But error is populated.
WHY:
i above query set we are executing alter and update command in one set. Now what happen at query parse, where it will check for the syntax and other parameters like column table existence.
When parser reach at UPDATE query it fail because "SAL_COMM" not found in the database.

SOLUTION:
Now what we can do,
we now the reason query fail because not existence of column. In that case we need to execute ALTER command before execution of UPDATE command.
This approach we get with using GO (batch seprator, where sql engine execute all query where GO placed), But issue is We can't use GO in BEGIN..END block.

SOOOO WE NEED TO DYNAMIC SQL, where parser get by-pass the UPDATE query.

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMP' AND COLUMN_NAME = 'SAL_COMM')
BEGIN
ALTER TABLE EMP ADD SAL_COMM int;
exec sp_executesql N'UPDATE EMP SET SAL_COMM = coalesce(SAL+COMM,sal,0)';
END

QV17 Subtract number from variable - set analysis expression

some times we need to subtract or add the number from the variable value. So i am posting this how we can subtract the number from set analysis expression in qlikview.

from this post we can learn
- how to use variable in set analysis
- how to subtract number from variable

Let have a look upon the matter
I have emp table and if you see the hiredate year variation from 1980 to 1987 as you can see in your below image.

 

I also have created calendar as in post 

http://j4info.blogspot.in/2014/09/qv16-create-calendar-dimension-using.html

after creation of calendar we have below data model.

After reload we have added below objects in the existing sheet. 

current selection is 1981 and variable vMaxYear store 1981 value in it. chart have below dimension
DEPTNO
HIREDATE

and expression :
chart1 - Sum ({$(=$(vMaxYear)-1)}>}SAL)

 will show the data of employees whose HIREDATE lie in the year 1980.

chart2 - Sum ({}SAL)
 will show all data, irrespective of any date selection.