QV26 Naming convention for any qlikview project

Prefixes                               
variables                Starts with a "v"             e.g.    vYear       
Key Fields               Starts with a "%"             e.g.    %companykey       
Flag Fields              Starts with a "_"             e.g.    _IsEnable       
Cycle Group              Starts with a "<"          e.g.    <YearMonth
Drilldown Group          Starts with a ">"          e.g.    >CountryStateCity
Key Field Separator      Separated by "_"              e.g.    Company&'_'&Nbr as Key       
Temp Fileds/Tables       End With "_tmp"               e.g.    Daily_Trans_tmp       
                                   
Field Names                               
Use business names for fields.                       e.g.    Customer Nbr instead of CustNo       
Rename fields in scripts where possible - not in charts and tables                                
                                   
Publisher Naming Standards                               
Abbr.  Abbr. Type     Meaning                       
All    Environment    Applicable to all environments                       
DEV    Environment    Development Environment                       
PRD    Environment    Production Environment                       
TST    Environment    Test Environment                       
APR    Publisher Item    Publisher Access Point Resource                       
JOB    Publisher Item    Publisher Job                       
SDF    Publisher Item    Publisher Source Document Folder Resource                       
TSK    Publisher Item    Publisher task                       
DSR    Publisher Item    Directory Service Resource                       
                                   

QV25 How to tune a qlikview application

We know that Qlikview is data pulling and other operations are quite faster than other tools. Still we need to optimize the qlikview document.

Reload, chart loading are depend upon various factor. Good data model also leads to high speed.

Some below factor we need to take care in document:

Reduce Rows	Eliminate unneeded data volume (rows) from apps (how far back does the app really need to store data?
Reduce Columns	Find unused fields in the data model - use DocumentAnalyzer_V1.5.qvw against your app to do this, then copy the "DROP FIELD" statements that are generated for the unused fields and place these into a new tab in YOUR application, as the VERY LAST tab in the script.
Reduce Distinctness	Reduce Distinctness - Reduce distinct values in your application - use the QlikView Optimizer.qvw against your app to find the fields that are candidates for these changes.   Select the "Symbols" in the Optimizer app to see these (timestamps, system keys, etc..)
Increase efficiency	Convert fields to numeric that are used in compare expressions, like IF(ClientType = 'Direct'….)
Reduce data model complexity	Collapse snowlfaked tables to attain a more pure star schema
Reduce App complexity	Reduce number of tabs/objects in the application, where appropriate
Reduce App complexity	Reduce the number of open charts down to 1 on tabs where this is possible and appropriate
Best Practices	Follow checklist items above in all areas

For Charts
1. Avoid Calculated dimensions - if possible use direct column in dimensions, calculated dimension leads to decrease the performance of table chart, because calculated dimension, do calculation for every row.

QV24 button - action "Toggle Select"

In script, we have Inline table, which contain field one, on which we are going to apply toggle to filter data from reports.

load * inline [
one, data
1,j
1,4
1,info
1,blog
0,spot
0,com
];
now create a sheet object- " button"
    - properties
    - Actions
    - add action
    - "Toggle select" (in field add field "one" and value in input box below field)
   

now add object on sheet "current select "
add object table box, add both fields in it.

now click on the button and see the behaviour in "chart" and "current selection box".

by default in table box it will show all rows including 1,0 . when we switch to toggle button it will show only 1 respective data in table box.
   

QV23 keepchar & purgechar in qlikview

replace equivalent in qlikview:

Want to remove unwanted chars from string?
Qlikview provide us very simple function like "keepchar" and "purgechar".

Keepchar - it will return only string, which we want to keep (we need to mention list of char, that we want to keep)
purgechar - it will remove unwanted chars from string (we need to mention list to char, that we dont want to keep)


examples:
=keepchar ( 'j%4in'(fo!','abcdefghijklmnopqrstuvwxyz0123456789' ) returns 'j4info'
=PurgeChar('j%4in'(fo!', '%(!' &chr(39))

*chr(39) - is used for remove single quote from string.

ROWNUM - Limiting Results and limitations

Rownum is mostly used pseudocolumn column, used for limit result like LIMIT in other databases and also used for TOP N-query.

Some people used rownum in query and ask why it don't return exact result they want. Before using rownum we should really know the working of rownum.

WORKING
ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N. A ROWNUM value is not assigned permanently to a row. 

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row: 

select * 
  from t 
 where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure: 

select ..., ROWNUM
  from t
 where 
 group by 
having 
 order by ;

Think of it as being processed in this order:
1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.
That is why a query in the following form is almost return wrong result:(emp list is not top most)

select * 
  from emp 
 where ROWNUM <= 5 
 order by sal desc;

LIMITATION 
ROWNUM only work with < in where clause

ROWNUM can not used for range like if you need result between some range

SOLUTION TO THIS use INLINE QUERY

TOP N result: 

below query give us list of top paid employees 

select *
  from  
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;