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.

string search in database postgres

I created a search string function in postgres, thru which we can search string in a database table having column datatype 'character', 'character varying', 'text'.

According to the requirement we can add more datatype to vast your search or you can cut down the data-type to narrow your search.

This is one requirement, which is covered.
Now using same function we can learn below topic

• for loop in postgres
• dynamic execution in postgres (EXECUTE)
• return record set using function using SETOF
•  To achieve this i learn that how to return multiple row (record set) from a function, i.e what i want to share with all of you guy

                    First of all we need to use SETOF with return keyword, second is need to declare a variable having record datatype. third is Need to use RETURN NEXT in for loop - it will return the rows to outer RETURN, which is earlier declared.

drop function seing(char(50))

CREATE OR REPLACE FUNCTION seing(str char(50))
RETURNS setof  character varying AS
$BODY$
DECLARE
    tempCount bigint ;
    record_v record;
    query text;
BEGIN
    query:= 'select table_name ,column_name from information_schema.columns where table_schema = ''public'' and
        data_type in (''character'',''character varying'',''text'')';
    FOR record_v IN EXECUTE query LOOP
        EXECUTE 'select count(*) as a from ' || quote_ident(record_v.table_name) || ' where ' || quote_ident(record_v.column_name ) || ' like ''%'||str ||'%'';' into tempCount;
        --RAISE NOTICE 'loop test .. it is working fine';
        return next record_v.table_name ||'    '   ||record_v.column_name  || '   '||tempcount ;
        --return   record_v.table_name ||' '||record_v.column_name;
    END LOOP;
return;
END;
$BODY$ LANGUAGE plpgsql VOLATILE
COST 100;

select seing('tbsp')

ERROR: cannot change return type of existing function

Error faced
ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION first.


this error generally comes when you trying to recreate the function having its RETURN TYPE value changed.

CREATE OR REPLACE FUNCTION seing(str char(50)) RETURNS TABLE (a varchar, b varchar) AS
$BODY$
DECLARE
.....


ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION first.
********** Error **********

ERROR: cannot change return type of existing function
SQL state: 42P13
Hint: Use DROP FUNCTION first.

Solution
drop function using drop command as below


drop function seing(char(50));

This is worked fine at my end. If you face the above error in future then always drop the function
as above command and
function name should contain the datatype as define in previous function.