Tuesday, August 19, 2014

QV10 Cross table

What is cross tables:
Cross tables are like that any 2-D sheet can be excel sheet or any feedback form. Where facts are divided on the basis of dimensions.

good example for this:
- sales record in excel sheet, month column is split over the column.
- any feedback form month wise arrange in a file. [feedback form for quality work upon various categories.]

Dire: LOAD Salesman,
Year
,
Jan
,
Feb
,
Mar
,
Apr
,
May
,
Jun

FROM
[..\datafiles\7_cross_table.xls]
(
biff, embedded labels, table is Sheet4$);

 
(example of cross table)

Why we use cross-table:
In qlikview if you have face the table like above, and if you trying to create the annual report on the basis of year which will act as a dimension.

So to create a meaningful report we need to denormalize  [means need to move column to rows]  the table, which is also called cross-table in qlikview.

For above screen shot , calculative data is split over the column which can-not be easily sumable w.r.t. dimension.





(We need to use "cross table " keyword to change the table orientation)
 
 
Dire:
CrossTable(category, Data,2)
LOAD Salesman,
Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun
FROM
[..\datafiles\7_cross_table.xls]
(biff, embedded labels, table is Sheet4$);

 *where - category is column name for jan-june columns

Below is chart created with cross table "Dire"




Friday, August 8, 2014

QV9 Dynamic Update

Again a great functionality in qlikview is Dynamic update. Through this we can write back to the dataset of qlikview. The written changes can be reflected in qlikview data model. Logically later we can write these changes can be written to qvd or xls or any other source file.
Below is my report UI


sub Insert
 set Result = ActiveDocument.DynamicUpdateCommand("INSERT INTO TEST  (SRNO,TYPE) VALUES (4,A)")
  if Result = false then
    MsgBox Result.ErrorMessage
  end if 
end sub

sub Update
 SQL_String="UPDATE TEST SET SR = 2 WHERE SRNO=3;"
  SET Result = ActiveDocument.DynamicUpdateCommand (SQL_String)
  if Result = false then
    MsgBox Result.ErrorMessage
  end if 
end sub

sub Delete
 SQL_String="Delete from TEST where SRNO =4;"
  SET Result = ActiveDocument.DynamicUpdateCommand (SQL_String)
  if Result = false then
    MsgBox Result.ErrorMessage
  end if 
end sub

sub SaveTableToFile
set obj = ActiveDocument.GetSheetObject("CH01")
obj.ExportEx "..\datafiles\6_dynamic_update.qvd", 4
end sub


INSERT INTO TEST (SRNO,TYPE) VALUES ($(vCnt), A $(vCnt))
UPDATE TEST SET CDATE = TODAY() WHERE SRNO=$(vCnt);
DELETE FROM TEST WHERE SRNO  = $(vCnt)

--script editor code copy and paste in below tab
// execute only once then comment it
//LOAD SRNO,
//    CDATE,
//    TYPE,SR;
//SQL SELECT *
//FROM SCOTT.TEST;
//
//
//store TEST into ..\datafiles\6_dynamic_update.qvd;

TEST:
LOAD SRNO,
     CDATE,
     TYPE,
     SR
FROM
[..\datafiles\6_dynamic_update.qvd]
(qvd);

set vCnt = 1;
 

Thursday, August 7, 2014

QV8 Partial Reload

Partial loading - as name specify the partial load instead of doing incomplete load, we will do incomplete load. Some times happen that we made changes to the database. The changes in the form of
- Create a new table
- New attributes added in the table
Now we want that table and new fields should reflect in qlikview datamodel. So Generally we do - we do reload off document.
But it is not a good deal if reload completes in hours. It will waste all the time.
So to save the time QlikView provide the partial load - Instead of reload we will load only those tables which had been modified in the database. This approach is called partial reload.
Advantage-
- While partial reload all other load statement and scripts remain untouched.
 Means partial will execute only those command where it find "ADD", "REPLACE" as a prefix to LOAD statement.

How partial Reload can be intialize
- ctrl+shift+r
- File > partial reload.

Two Approaches are there:
- use "ADD", "REPLACE" in front of LOAD statement (partial reload and reload both can execute this statement)
- We can also use "ADD ONLY", "REPLACE ONLY" instead of "ADD", "REPLACE"
 By adding "ONLY" , then the command will execute only and only when we do partial Reload of document. Reloading of document will not affect these Load statements.

- AND we can use IsPartialReload() function:
 it will return -1 --- when we do partial reload  (ctrl+shift+r)
   return 0 ---- when we do reload (ctrl+r)

  
--With partialreload function .. put below code in script editor  
if (IsPartialReload() = -1 ) then
add LOAD GRADE,
    LOSAL,
    HISAL;
SQL SELECT *
FROM SCOTT.SALGRADE;
end if;


Above code will be execute only and only if we do partial reload.


-- With and ADD and REPLACE keyword
//below commented statement is load once with (ctrl +R), it load 3 column in data model.
//dasf:
//replace
//LOAD SRNO,
//    CDATE,
//   TYPE;
//   SQL SELECT *
//FROM SCOTT."TEST";


dasf:
replace only
LOAD SRNO,
    CDATE,
   TYPE ,SR;
   SQL SELECT *
FROM SCOTT."TEST";


fligh:
add only
LOAD "PRIMARY_KEY",
    "CREATED_DATE",
    "UPDATED_DATE",
    YEAR,
    MONTH,
    "AIRCRAFT_TYPE_ID",
    "DEPARTURES_SCHEDULED",
    "DEPARTURES_PERFORMED",
    "AVAILABLE_SEATS",
    "TRANSPORTED_PASSENGERS",
    "TRANSPORTED_FREIGHT";
SQL SELECT *
FROM SCOTT."FLIGHT_DATA_1";


- Now save it and do partial reload (ctrl+shift+r)

It is suggest to check the Data model along with every reload.

QV7 Generic load in qlikview

A generic table is commonly used to store attribute values for different objects.
eg. ball and box is the example

These attributes are not necessarily shared across all objects. Objects might have common field or not.
Let have we have two objects ball and box
ball attribute - color , weigth , diameter
box attribute - color , weigth , length, heigth, width

Here we can we color and weigth are common field between two objects. And the attributes and its values store in a single table as below.




Enter the Generic prefix.
It converts the data to a structure where each attribute is placed in a field of its own (each attribute now act as a an object entity). Another way to express it is to say that it takes field values and converts these to field names. If you compare it to the Crosstable prefix, you will find that they in principle are each other’s inverses.


The syntax is
   Generic Load Key, Attribute, Value From … ;


There are however a couple of things worth noting:
    Usually the input data has three columns: one qualifier field (Key in the above example), an Attribute and a Value. But you may also have several qualifying fields. If you have four or more columns, all columns except the two last will be treated as qualifying fields.
    The Generic prefix will create several tables; one table per attribute. This is normally not a problem. Rather, it is an advantage: It is the least memory-consuming way to store data if you have many attributes.


Now open qlikview and create new document save it. and open script editor
// generic load with one consolidated resulting table:
InputTable:
LOAD * INLINE [
object,attribute,value
ball,color,red
ball,diameter,10 cm
ball,weight,100 g
box,color,black
box,height,16 cm
box,length,20 cm
box,weight,500 g
box,width,10 cm
];
 

GenTable:
Generic Load object, attribute, value Resident InputTable;
 



 
after reload check data model


Now as you can see in image, every attribute act as its own object entity.

after search alot on google i have found that the use of generic load is very rare. It can be use only when we want to change the orientation of the table.





Paste below code in same script editor to made orientation in table.
ResultTable:
LOAD Distinct object Resident InputTable;
 

FOR i = 0 to NoOfTables()
 TableList:
 LOAD
  TableName($(i)) as Tablename AUTOGENERATE 1
 WHERE
  WildMatch(TableName($(i)), 'GenTable.*');
NEXT i


FOR i = 1 to FieldValueCount('Tablename')
 LET vTable = FieldValue('Tablename', $(i));
 LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];
 DROP TABLE [$(vTable)];
NEXT i

source -
http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

Wednesday, August 6, 2014

QV6 Preceding Load

What is Preceding load
- preceding load allow you define multiple transformations and calculations within one load script,
if it is not present we need to create another load or resident statement.

BENIFITS, WHY PROCEDING LOAD
- we can use multiple load using previous loaded select
- Advantage of Preceding Load is that it allows you to use QlikView functions in the Load script
  (on other hand you can not use qlikview function in SQL load query)
- USED FOR TRANSFORMATION and calculations PURPOSE
- It is also suggested to use preceding load to some scenerio, in which the qlikview function are using again and again like below example,
 1st load statement from below using date function and 2nd load statement to get the difference.
 We can also write the '1st load statement from below' as

LOAD date(CREATED_DATE,'D-MMM-YYYY') AS  [CREATED DATE] ,Today(1) AS TODAY_DATE , date(UPDATED_DATE,'D-MMM-YYYY') AS [UPDATED DATE],Today(1)-date(UPDATED_DATE,'D-MMM-YYYY') AS DIFF;


Which cause the overhead for qlikview and we are processing the data, which is calculated primarily.



1. Open Qlikview, create new document and save it.
2. Press ctrl+e (now you can see script editor)
3. create the respective ODBC connection to the database having table "flight_Data_1"
4. You can get "FLIGHT_DATA_1" create table script from post http://j4info.blogspot.in/2014/08/qv5-incremental-loading.html
5. Copy and paste below code in script editor


LOAD "PRIMARY_KEY", IF ([DAYS DIFF] > 170 , 'YES','NO'),[DAYS DIFF];
LOAD "PRIMARY_KEY", TODAY_DATE - [UPDATED DATE] AS [DAYS DIFF];
LOAD "PRIMARY_KEY",date(CREATED_DATE,'D-MMM-YYYY') AS  [CREATED DATE] ,Today(1) AS TODAY_DATE , date(UPDATED_DATE,'D-MMM-YYYY') AS [UPDATED DATE];
SQL SELECT
 "PRIMARY_KEY",
    "CREATED_DATE",
    "UPDATED_DATE",
    YEAR,
    MONTH,
    "AIRCRAFT_TYPE_ID",
    "DEPARTURES_SCHEDULED",
    "DEPARTURES_PERFORMED",
    "AVAILABLE_SEATS",
    "TRANSPORTED_PASSENGERS",
    "TRANSPORTED_FREIGHT"
FROM SCOTT."FLIGHT_DATA_1";



Now you can see in above code we have three preceding load statements. Preceding load statement execute below to up manner and order of execution is get by qlikview itself it search for the "from /Resident " clause for respective load statement until it get.
 - it load table from database using SQL SELECT query
 - First load statement converting raw date to actual date format.
 - 2nd load statement getting date difference between today date and updated date.
 - 3rd Load statement using if condition if DATEDIFF > 170 then return "yes" else "no"

 
web stats