Average Total %

[tracycrown]

Dear all

Kindly advise how to compute the correct average total% of 20%, 16.7% is wrong.

Thank you, Tracy

[Vegar · Accepted solution]

I don't have the tool to open your sample qvw at the moment 😔,  but  the looks of your Excel I suggest that you have trouble getting the correct average due to one row with no value.

Try these expressions

• avg(Rental)/sum(total Rental)
• avg(total Rental)/sum(total Rental)
• (avg(Rental)/sum(total Rental))-avg(total Rental)/sum(total Rental)

[tracycrown · Accepted solution]

Dear Mr Vegar

You have resolved all my problems. Thank you very much, you are the best.

Can you advise the following with examples :

1. Eliminate all records in script for records without value.

2. Replace all records in script with zero for records without value instead of '-' 

Some suggested using Alt (Rental) or Isnull(Rental), can you also give me some examples.

Thank you very much, Tracy

[BrunPierre · Accepted solution]

@tracycrown This normally does the trick of restricting the load of the null value.

LOAD *, FROM Source Where Len(Trim(Rental)) > 0 ;

Indeed, You can replace any NULL value with 0 (zero) by using the function ALT, in your case, like this.

Alt(Rental, 0) as Rental

This will be efficient where there are multiple fields.

SET NULLINTERPRET='-';
NullAsValue Rental, Field1,Field2 ;
Set NullValue = 0;

[tracycrown]

Dear Mr BrunPierre

Can you use my attached rental file to illustrate your solution ?

Thank you, Tracy

[BrunPierre]

Of the two, this is a better approach

LOAD Factory,
Rental

FROM [...\Data-1.xls] (biff, embedded labels, table is Sheet1$)
Where Len(Trim(Rental)) > 0 ;

than this;

LOAD Factory,
Alt(Rental, 0) as Rental

FROM [...\Data-1.xls] (biff, embedded labels, table is Sheet1$);

[tracycrown]

Dear Mr BrunPierre

Qlik cannot accept than this, please see below :

LOAD
Factory,
Rental
FROM
[Data-1.xls]
(biff, embedded labels, table is Sheet1$)
Where Len(Trim(Rental)) > 0 ;
than this; <----------------- error

LOAD
Factory,
Alt(Rental, 0) as Rental
FROM
[Data-1.xls]
(biff, embedded labels, table is Sheet1$);

Regards, Tracy

[BrunPierre · Accepted solution]

They're two different load scripts for handling null values.

This restricts the load of null values,

---

LOAD Factory,
Rental

FROM [...\Data-1.xls] (biff, embedded labels, table is Sheet1$)
Where Len(Trim(Rental)) > 0 ;

While this replaces any NULL value with 0.

LOAD Factory,
Alt(Rental, 0) as Rental

FROM [...\Data-1.xls] (biff, embedded labels, table is Sheet1$);

[tracycrown]

Dear Mr BrunPierre

For Multiple fields, am I correct to put the below statements at the beginning of the script before loading the file. For example : 

SET NULLINTERPRET='-';
NullAsValue Rental, Act_Value, Bud_Value ;
Set NullValue = 0;

Load Factory,

Rental,

Act_Value,

Bud_Value

FROM
[Data-1.xls]
(biff, embedded labels, table is Sheet1$)