Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I am trying to understand following script:
table_2:
Load
Alfa,
Autonumber(Alfa) as AlfaNum,
Num
resident table_1;
In above script unable to understand the use of Num.
Pls help me on this.
Thanks,
Num looks like a column name, not a function...
Hi,
I think Num is a column name in this context, check script for table_1 you will find the answer.
Regards,
Jagan.
Refering following section from QlikView Optimization Best Practices.pdf available on community :
**********************************************************************************************************************
Case1. Count( Distinct ‘FieldName’).
The distinct qualification, especially if text strings are read, is costly. A useful technique
is to assign the value ‘1’ to each new value as the field is read:
Load
Alfa,
if (peek('Alfa')=Alfa,0,1) as Flag1,
Num
resident table_1
order by Alfa Asc;
Here the “peek” compares the value of Alfa being read with that previously read. If the
values are the same “Flag” is set to 0, if they are different “Flag” is set to 1. The number
of distinct values will then be = sum(Flag). Please note that the list has to be ordered
and that when using “order by” in a load resident QlikView orders the list before starting
to read.
Another method:
Load distinct
Alfa,
Alfa as AlfaDist
resident table_1;
Now Count( Distinct Alfa ) can be replaced by a simple count: Count( AlfaDist ).
Notice that Alfa is read twice, once with the original name to link to the original table,
once with a new name to allow Count(). (Linking fields not allowed in Count()). All other
fields must also be left out as they would degrade the distinct clause.
A third method is to give each distinct value of “Alfa” a numeric value:
table_2:
Load
Alfa,
Autonumber(Alfa) as AlfaNum,
Num
resident table_1;
Count( Distinct AlfaNum) is a cheaper operation than Count( Distinct Alfa) since the
comparison is of numeric values. An even cheaper method is to find the last (or largest)
result of the autonumber function.
set AlfaDistinctCount = peek( ‘AlfaNum’, -1, ‘table_2’ );
in the script or as expression:
max( AlfaNum)
in a layout object.
************************************************************************************************************************************
Pl Let me know if you have got clarity. Thanks