Sunday, January 11, 2015

A Little More Optimized Load

This is a continuation of last week’s blog topic (search on "QlikView Maven Optimized QVD Load With a Where Clause and Dates"). 
I had a load statement in a loadscript loading from a qvd file that (simplifying a bit) looked like this:

INVOICE_DATA:   
Load CUST_NO,
ApplyMap('custprimap',CUST_NO) as CUST_PRIORITY,
INVOICE_NO,
INVOICE_AMT
from invoice_data.qvd (qvd) 
where exists(INV_DATE); 

It was written using a mapping table feature for customer data to avoid a left join load for performance reasons. But, the load from the qvd file wasn’t an optimized load because of the ApplyMap function. The table contained many millions of rows and using an optimized load would save quite a bit of time. 
I was able to get an optimized load and still have the ApplyMap functionality by recoding that portion of the loadscript to also use a Join and a super-fast load from FieldValue like this: 

INVOICE_DATA:   
Load CUST_NO, //optimized load
INVOICE_NO,
INVOICE_AMT
from invoice_data.qvd (qvd) 
where exists(INV_DATE);

Join (INVOICE_DATA) load //preceding load
CUST_NO,
ApplyMap('custprimap',CUST_NO) as CUST_PRIORITY; 
Load FieldValue(CUST_NO,IterNo()) as CUST_NO
Autogenerate(1) while not isnull(FieldValue(CUST_NO,IterNo()));



______________ _________________ ________________

Note to Santhosh:  You can add special comments to a chart expression that will make it easier to find specific expressions with the Settings->Expression Overview, Find feature. For example, you might use an expression like this:
sum(CONTRCT_S1 * CNTRY_ADJ) //per NATO procurement 1987.36.c
and then it would be easier to find the expressions that need to be changed when procurement rules change by doing an Expression Overview Find on "NATO procurement".
Our otherwise well-intentioned friends who believe all chart expressions should be contained within variables cannot easily use this suggestion.

  ★★★

4 comments:

Bill Markham said...

Looks interesting.

I have never used the FieldValue() function and on looking it up in the Help saw the warning: "Note: This function will only work with distinct field values."

So in your example does mean that it will only work if field CUST_NO does not have any duplicate values, or am I misunderstanding ?

-TB said...

Hi Bill,
The Help text means that the function will return only distinct values. Some of the tables I work with have millions of rows that all share the same value for some fields. The FieldValue function works fine and the fact that it returns all of the distinct values of a field is part of its value.
-Tim (Maven)

Ignacio Albano said...

Hi Tim,
What about using the MAP USING statement before loading the Fact table?
Did you try it?
BR,
Ignacio

Anonymous said...

Hi TB,
Nice interesting post. always use apply map function avoiding joining condtion. if not we can directly join from custprimap table instead create autogenerated table and bring new field from custprimap table through Applymap function. Like below we can use is correct ?

Join (INVOICE_DATA) load // load
CUST_NO,
CUST_PRIORITY;
From custprimap ;
I am little confusing ,please correct if am I wrong ?