應用程式效能最佳化

Count (Distinct 'FieldName')

Count(distinct fieldname) 一般來說是高度耗用效能的操作，對於測試欄位更是如此。盡可能將 count () 和 distinct 限定詞取代為 sum() (將值 '1' 指派至每個在指令碼中讀取的相異項目)。

Load

Alfa,

if (peek('Alfa')=Alfa,0,1) as Flag1,

Num

resident table_1

order by Alfa Asc;

Load distinct

Alfa,

Alfa as AlfaDist

resident table_1;

table_2:

Load

Alfa,

Autonumber(Alfa) as AlfaNum,

Num

resident table_1;

Count( Distinct AlfaNum ) 比起 Count( Distinct Alfa ) 來說，是較不耗費資源的操作，因為比較的是數值。而更不耗費資源的方法是尋找 autonumber 函數最後的 (或最大的) 結果。

set AlfaDistinctCount = peek( ‘AlfaNum’, -1, ‘table_2’ );

max( AlfaNum)

If ( Condition(Text),....)

If (Alfa= ‘ABC’, ‘ABC’, left (Alfa, 2))

Load

*,

If (Alfa = ‘ABC’, 1, 0) as Flag

resident table_1 ;

If ( Flag = 1,’ABC’, left (Alfa, 2))

Sum ( If (Condition, ‘FieldName’…))

Sum ( If (Alfa= ‘ABC’, Num*1.25 , Num) )

Load

*,

If (Alfa = ‘ABC’, 1, 0) as Flag

resident table_1 ;

Sum ( If ( Flag = 1, Num* 1.25 , Num ) )

table_2:

Load

*,

If (Alfa = ‘ABC’, 1, 0) as Flag

resident table_1 ;

table_3:

Load

Alfa,

If ( Flag = 1, Num* 1.25 , Num ) as NewNum

resident table_2 ;

table_4:

Load

Alfa,

Sum( NewNum ) as SumNum

resident table_3

group by Alfa ;

If ( Condition1, Sum(‘FieldName’), If (Condition2, Sum(‘FieldName’)……..

sum((GAC12_STD_COST * GAC15_EXCHANGE_RATE) * GIV24_DISP_QTY)

Replaces

Sum(

If((GAC12_EFCT_DT<= GIV23_REJ_DT and

GAC12_EXPIRE_DT>GIV23_REJ_DT) and

(GAC15_EFCT_DT<= GIV23_REJ_DT and GAC15_EXPIRE_DT>GIV23_REJ_DT),

GAC12_STD_COST * GAC15_EXCHANGE_RATE) * GIV24_DISP_QTY,

Null()))

and

Sum(

If(GAC12_EFCT_DT<= GIV23_REJ_DT,

If(GAC12_EXPIRE_DT>GIV23_REJ_DT,

If(GAC15_EFCT_DT<= GIV23_REJ_DT,

If(GAC15_EXPIRE_DT>GIV23_REJ_DT,

(GAC12_STD_COST * GAC15_EXCHANGE_RATE) * GIV24_DISP_QTY,

Null())))))

排序文字

QlikView 會自動評估要以數值文字通用格式來處理某個欄位。評估為文字的欄位會視為文字加以排序，而這是最緩慢的排序操作。這可手動取代為按載入順序來排序。如果並不需要對列表框等加以排序，可關閉排序功能。

QlikView 會將混合字元和數字的字串，以英數字元的順序加以排序。也就是說，數字是以值順序排序，非數字則是以 ASCII 順序排序，而非傳統上僅使用 ACII 排序順序。範例：

ASCII 排序 英數字元排序
A1 A1
A10 A4
A11 A5
A30 A6
A4 A10
A5 A11
A6 A30

動態標題和文字物件

If ( ReloadTime()+3>Now(), 'Old Data', 'New Data')

If ( ReloadTime()+3>Today(), 'Old Data', 'New Data')