Qlikview static maps reporting

Hi

While trying google map over qlikview  , i faced  with a web-view error ( i guess because of IE10 or something like that) so  i decided to explain how can we  show  our data  over static mapping reports in qlikview.


At first I am gonna prepare my data over an excel file:

And now come back to Qlikview i add following script to read data in excel

After loading our script we have following fields:

Now we gonna choose an image for our scatter chart.In below  I can see the coordinates of countries.By using  that info  i am gonna create my temp data for country coordinates.

And now i add my temp data into script section.

After loading script  , i am adding my scatter chart into my sheet , and specify settings as follows.

1) Choose  Scatter Chart

2)Dimension tab I am adding COUNTRY field.

3) For expression tab i am adding  X , Y and SUM(GDP)  as follows
(In my map I just wanna see SUM(GDP) value as popup so i checked the "Text as Popup" for SUM(GDP)

4) Now for presenting my chart i disable Show X-Y label in order to get a better map view.

5)Axis tab  should be set as follows. (Scale values has to be specified otherwise data cannot pin the correct spot on the map )

6) Last part  we gonna set image for our chart in Colors tab.
(Color-->Frame Backgroud--> Image--> ( Set File ) & Check Plot Area Only

7)Final screen would be look like this

tableau - TOP N filter over BarChart

Hi

In this entry i will show how to implement a "top n filter" object over our reporting sheet.

First of all , here below we have following chart which contains sum(profit) over region and country for each person.

Now what i want to do is to show Top 2 profits  for each person.


Step1
In data panel  creating  a "Calculated Field" and use following formula.

Step 2
Dragging  created object (TOP-N filter) into Filters part.

Step 3
Right click onto TOP-N filter  click "edit table calculation"  and
choose  Compute Using --> Advanced

Step 4
Advance section we gonna select partition and field values in order to define our ranking.

Finally Top-n filter is ready.
Now i am gonna define the range which is top 2 values and Results will be look like this...

tableau Date Formating

Hi

In this entry i will show how to format date in tableau reporting screen.

At first I choose excel as my datasource  and  use Orders (Sample - EU Superstore) and I want to see people who have made successful orders and details about it.

In here we have  a column called  ShipDate  and now i want to create a variable  that only shows me YEAR.MONTH information for  aggregations.


Formula for calculated Field:

Result:

Oracle Finding first day of the month

Hi

Following sql helps you to find first day of previous months.


select  trunc(sysdate) from dual

22.01.2016

First Day of Current Month:
select    ADD_MONTHS (   LAST_DAY (  TRUNC (SYSDATE)  )  ,   -1  )+1   from dual

01.01.2016                                                                    

First Day of  eleven months before:                                      
select    ADD_MONTHS (   LAST_DAY (  TRUNC (SYSDATE)  )  ,   -11  )+1   from dual

01.03.2015

Oracle Finding Last_Day of Previous months

Hi

Following sql helps you to get previous months  last_day values:

Current Date:
select  trunc(sysdate) from dual

22.01.2016

Results:
-- One month before
select   ADD_MONTHS (LAST_DAY (TRUNC (SYSDATE)),-1)   from dual
31.12.2015

-- Eleven months before
select   ADD_MONTHS (LAST_DAY (TRUNC (SYSDATE)),-11)   from dual
28.02.2015

Oracle - CREATE NLS_CALENDAR Table

Hi

In this entry i will explain how can we create a NLS_CALENDAR table  , which can be used in ETL procedures as a lookup table.

At first lets create our table :

(
Table contains DAY , MONTH,YEAR and FULL_DATE values
Partition by YEAR and  Subpartitioned by MONTH
)

CREATE  TABLE   NLS_CALENDAR_TABLE
(
DAY  NUMBER,
MONTH  NUMBER,
YEAR NUMBER,
FULL_DATE  DATE
)
PARTITION BY RANGE ( YEAR )
        SUBPARTITION BY LIST (MONTH)
                SUBPARTITION TEMPLATE
                (
                        SUBPARTITION  MONTH1  VALUES(1),
                        SUBPARTITION  MONTH2  VALUES(2),
                        SUBPARTITION  MONTH3  VALUES(3),
                        SUBPARTITION  MONTH4  VALUES(4),
                        SUBPARTITION  MONTH5  VALUES(5),
                        SUBPARTITION  MONTH6  VALUES(6),
                        SUBPARTITION  MONTH7  VALUES(7),
                        SUBPARTITION  MONTH8  VALUES(8),
                        SUBPARTITION  MONTH9  VALUES(9),
                        SUBPARTITION  MONTH10  VALUES(10),
                        SUBPARTITION  MONTH11  VALUES(11),
                        SUBPARTITION  MONTH12  VALUES(12)
                )
        (
        PARTITION YEARXXX VALUES LESS THAN (2012)
)

Second step PL/SQL code for filling the table :

Note that  NLS_DATE_FORMAT for following code is 'DD.MM.YYYY'


DECLARE
BEGIN_DATE DATE;
END_DATE  DATE;
V_DYNTASK LONG;
VAL1  DATE;
DIFF_VAL  NUMBER;
DAY_VALUE  NUMBER;
MONTH_VALUE  NUMBER;
YEAR_VALUE  NUMBER;
YEAR_PART_VAL  NUMBER;
YEAR_VALUE_TMP NUMBER ;
BEGIN


BEGIN_DATE:= '01.01.2012';
END_DATE:='01.01.2050';  --- NOT INCLUDED
YEAR_VALUE_TMP:=0;

V_DYNTASK:='SELECT TO_DATE('''||END_DATE ||''') -  TO_DATE('''||BEGIN_DATE||''') FROM  DUAL ';
EXECUTE IMMEDIATE V_DYNTASK INTO DIFF_VAL;


FOR  COUNTER1 IN  0..DIFF_VAL-1
LOOP

        V_DYNTASK:= 'SELECT  TO_DATE('''||BEGIN_DATE||''')+'||COUNTER1||'  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO VAL1;



        V_DYNTASK:=  'SELECT  EXTRACT(DAY  FROM  TO_DATE('''||VAL1||''',''DD.MM.YYYY'') )  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO DAY_VALUE;

        V_DYNTASK:=  'SELECT  EXTRACT(MONTH  FROM  TO_DATE('''||VAL1||''',''DD.MM.YYYY'') )  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO MONTH_VALUE;

        V_DYNTASK:=  'SELECT  EXTRACT(YEAR  FROM  TO_DATE('''||VAL1||''',''DD.MM.YYYY'') )  FROM DUAL';
        EXECUTE IMMEDIATE V_DYNTASK INTO YEAR_VALUE;

     
   
         IF   YEAR_VALUE_TMP!=YEAR_VALUE
         THEN
           
            YEAR_PART_VAL:=YEAR_VALUE+1;
            V_DYNTASK:= ' ALTER TABLE NLS_CALENDAR_TABLE   ADD PARTITION YEAR'||YEAR_VALUE||'  VALUES LESS THAN  ( '||YEAR_PART_VAL||' ) ' ;
            DBMS_OUTPUT.PUT_LINE(V_DYNTASK);
            EXECUTE IMMEDIATE  V_DYNTASK;
            COMMIT;
       
         END IF;
     
     
     
       V_DYNTASK:=
       '
       INSERT INTO  NLS_CALENDAR_TABLE  ( DAY ,  MONTH, YEAR ,  FULL_DATE )
       SELECT  '
       ||DAY_VALUE||'  ,  '||MONTH_VALUE||'  ,  '||YEAR_VALUE||' , '''||VAL1||'''  FROM  DUAL';
     
     
       EXECUTE IMMEDIATE V_DYNTASK;
       COMMIT;

       YEAR_VALUE_TMP:=YEAR_VALUE;  --  CHECK FOR YEAR IS CHANGED OR NOT  , IF YES THEN ADD PARITION
     
END LOOP;


END;


Result will be look like this:

Also Final Table Structure:

Oracle - Big Table Joins with Nested Loop

Hi

In this entry  i am gonna show faster execution sample for big data queries.

Environment: Oracle 11g


First we have the following SQL sample ( try to join 50M vs 88M data  which takes  7 minutes to return with parallel hint  )

select  /*+parallel( m1 , 12)  parallel( s1 12) */  *
from
test_table1  m1 ,    --50 million rows
test_table2 s1         --88 million rows
where
m1.col1=s1.col2   --Return Time : 7minutes

By Default optimizer choose HASH_JOIN which causes long execution time

Plan
SELECT STATEMENT ALL_ROWS Cost: 11 M Bytes: 268092 G Cardinality: 90 G
	3 HASH JOIN Cost: 11 M Bytes: 268092 G Cardinality: 90 G
		1 TABLE ACCESS FULL TABLE TEST_TABLE2 Cost: 474 K Bytes: 109 G Cardinality: 97 M
		2 TABLE ACCESS FULL TABLE TEST_TABLE1 Cost: 446 K Bytes: 114 G Cardinality: 61 M

To perform better first we will create an index  over join column (COL1)  in our smaller table.( TABLE1)

Then over same statement  , we add USE_NL hint  and we got the result as follows:

select   /*+ USE_NL( m1, s1)*/  *
from
test_table1  m1 ,
test_table2 s1
where
m1.col1=s1.col2   --   Return Time: 421 MSEC  (Much faster !! )

Plan
SELECT STATEMENT ALL_ROWS Cost: 72 G Bytes: 268092 G Cardinality: 90 G
	4 TABLE ACCESS BY INDEX ROWID TABLE TEST_TABLE1 Cost: 1 K Bytes: 2 M Cardinality: 923
		3 NESTED LOOPS Cost: 72 G Bytes: 268092 G Cardinality: 90 G
			1 TABLE ACCESS FULL TABLE TEST_TABLE2 Cost: 474 K Bytes: 109 G Cardinality: 97 M
			2 INDEX RANGE SCAN INDEX X_INDEX Cost: 2 Cardinality: 1 K