Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all
Kindly advise how to compute the correct average total% of 20%, 16.7% is wrong.
Thank you, Tracy
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
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
@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;
They're two different load scripts for handling null values.
This restricts the load of null values,
LOAD Factory,
RentalFROM [...\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 RentalFROM [...\Data-1.xls] (biff, embedded labels, table is Sheet1$);
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
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
@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;
Dear Mr BrunPierre
Can you use my attached rental file to illustrate your solution ?
Thank you, Tracy
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$);
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
They're two different load scripts for handling null values.
This restricts the load of null values,
LOAD Factory,
RentalFROM [...\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 RentalFROM [...\Data-1.xls] (biff, embedded labels, table is Sheet1$);
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$)