Gotcha – (qvd) is an important part of LOAD QVD syntax

 

When using a LOAD statement that targets a QVD file the typical syntax is:

LOAD

*

FROM

Qvd_path_filename.QVD (qvd)

;

 

Please don’t forget that “(qvd)” part. Even though the syntax of the LOAD command denotes it is optional and QVW will not complain about its absence, unpredictable things can happen in the LOAD statement when it isn’t stated.

 

Without it, you may see only a subset of my QVD rows returned even though no syntax is in place to restrict the rows returned.

 

Bad:

LOAD

*

FROM

[Qvd_path_filename.QVD]

;

may return only a small subset of rows

 

Good:

LOAD

*

FROM

[Qvd_path_filename.QVD] (qvd)

;

 

Other symptoms of when “(qvd)” is not used are the QVD’s XML header being read as text and not the binary contents of the QVD and the QVD file not being reported as inaccessible despite its existing and being accessible to QVW

Tip – Always state CONCATENATE or NOCATENATE

 

Every LOAD statement should state its intention to concatenate or not via the CONCATENATE and NOCATENATE keywords unless assumed concatenation is the intended course of action in which case a /* comment */ should be placed near LOAD to remind the developer.

QVW assumes that you want to concatenate if the new table being loaded has identical field names to a currently loaded table. (The field sequence or contents doesn’t matter, only the names, including capitalization of those names). This can be what you intended but often it is not. QVW will not warn, log or otherwise indicate that it has assumed you want to concatenate and that can be an issue when you didn’t intend concatenation to happen.

Naming the new table in the LOAD statement does not prevent concatenation either.

Problem1:

Table1:

LOAD

field1

<etc>

;

Table2:

LOAD

field1

<etc>

;

 

Table1 will exist but Table2 wont. Table1’s contents are automatically concatenated with the contents of the LOAD statement for Table2. Table2 itself is never created as a separate table.

Solution:

Table1:

NOCONCATENATE

LOAD

field1

<etc>

;

Table2:

NOCONCATENATE

LOAD

field1

<etc>

;

 

Both Table1 and Table2 will exist.

 

See Tip – “Detecting QVW’s automatic renaming of tables” with a procedure that will help your code be more aware that QVW did something unexpected.

Tip - Selectively limit number of rows loaded in LOAD script

Tip - Selectively limit number of rows loaded

Although the EDIT SCRIPT\DEBUG\LIMITED LOAD feature can restrict the number of rows return from all LOAD statements in a script, it has its limitations:

·        It limits every LOAD statement in the script equally i.e. no selectivity

·        It does not remember the last setting e.g. it always defaults to 10

·        It cannot be turned on by default

·        It limits the rows returned from SQL SELECT after the database has done all the work to prepare a larger number of rows

·        The dialog box makes the LOG entry window very small and therefor of limited use

·        It cannot be parameterized to allow a complete run of QVW tasks to be executed using limited or empty LOAD statements to allow a syntax and semantic check across the whole application

·        It can substantially slow down script execution

 

The tips below allow more flexibility in limiting rows and resources used on a per LOAD basis.

 

Set up the following variables:

 

let v_max_rows =100;
//let v_max_rows =;  // use this to turn it off

let v_limit_number_of_rows_sql = if( IsNull (v_max_rows), NULL, 'FETCH FIRST ' & v_max_rows & ' ROWS ONLY' ) ;

let v_limit_number_of_rows_qvw = if( IsNull (v_max_rows), ' /* dont restrict row count */', ' FIRST ' & v_max_rows & ' ' ) ;
 

 

And use them as described in LOAD statement examples below. Now, when you use RELOAD, you all your LOAD statements will be limited to XXX rows.

 

To turn off this feature, simply set the ‘v_max_rows‘ variable to NULL (see examples above):

               let v_max_rows =; 

Notes:

1.      Use of QVW ‘FIRST’ syntax does not prevent optimized loads

2.      Although ‘FIRST’ could also be used in SQL LOADs, it would apply after DB2 has sent all the rows across so it’s best to use DB2 specific ‘FETCH FIRST xxx ROWS ONLY’ syntax embedded in the SELECT

3.      The QVD FIRST also works within a CONCATENATE LOAD etc.

 

For LOAD from QVD:

       $(v_limit_number_of_rows_qvw)

       load
             etc
       FROM
             [filename.QVD] (qvd)
       WHERE
             etc
       ;
For LOAD FROM SQL:

LOAD

etc

;

SQL

SELECT

etc

$(v_limit_number_of_rows_sql)
with UR;

 

 

If your script uses DEBUG MODE’s LIMIT LOAD feature and FIRST XXX commands, you may encounter errors if you set your LIMIT LOAD value lower than any hardcoded FIRSTXXX syntax

 

E.g.

FIRST 1000

LOAD *

<some table or another>

 

And DEBUG LIMIT LOAD=999

 

This combination will generate an error similar to below:

Script line error:

claim:

 FIRST 1000

LOAD

     *,

     CLM_ID as CLM_ID_MATCH

FROM

     [..\QVD\Claim_Weekly_Valid.QVD] (qvd)

 

 

QVW’s warns that it’s been asked for two contradictory things i.e. limited the load to 999 rows and limit to 1000 rows.

 

QVW has no issues with setting LIMIT LOAD to a higher value than any stated FIRST XXX syntax e.g. LOAD LIMIT=2000 and FIRST 1000 combination issues no error and the FIRST XXX statement will take precedent

 

Note:  Neither LIMITED LOAD or FIRST keywords can reduce the results returned from the BINARY keyword.

Tip  - Do not mix case when naming Qlikview variable/identifiers  

 

QlikView’s has a case sensitive scripting language i.e. the case of a variable/identifier is important e.g. “QlikView”, “QLIKVIEW”, “qlikview” are all considered different identifiers to QlikView.  

  

This, in itself is not an issue, but when combined QlikView’s absence of mandatory variable declaration can cause issues. A simple typo can cause QlikView to automatically create a new variable with a slightly different name from the one you were expecting leading to time consuming troubleshooting.  

  

The recommendation is to always use consistent case i.e. all lower, or all upper and if you need to separate words in an identifier name then use the underscore character. This recommendation differs from all QlikView training and reference materials.  

  

Examples:  

let ProcessingPeriodEndDate = Date( WeekEnd(Today(),-1), 'MM/DD/YYYY') ;   

let ProcessingPeriodEndDatetime   = Date(ProcessingPeriodendDate, 'YYYY-MM-DD') & '-23.59.59.999999';     

Can you spot the error? The 2nd reference to “ProcessingPeriodEndDate” has a typo and QlikView will attempt to get a value from this new variable instead of the one we want it to.  

 

Highlighted: 

let ProcessingPeriodEndDatetime   = Date(ProcessingPeriodendDate, 'YYYY-MM-DD') & '-23.59.59.999999';    

Typos regarding case are less likely if the same case is used throughout the naming convention because they jump right out at you in the script:  

let v_processing_period_end_date = Date( WeekEnd(Today(),-1), 'MM/DD/YYYY') ;   

let V_processing_period_end_datetime   = Date( v_processing_period_End_date, 'YYYY-MM-DD') & '-23.59.59.999999';     

Can you spot the typos now?  

Highlighted: 

 let V_processing_period_end_datetime   = Date( v_processing_period_End_date, 'YYYY-MM-DD') & '-23.59.59.999999'; 

 

note: Script syntax FORCE CASE e.g. FORCE CASE UPPER or FORCE CASE LOWER, will control the allowable case on field names but there appears to be no obvious equivalent for variable names