Solved: Total Averages in Table - Data Clean for Zero and ... - Qlik Community

Report Inappropriate Content · ‎2016-07-04

Dear All,

I have read something about how Qlik is handling null(). My Excel sheet I am loading from has records containing 0 or "-".

So I would like to clean my data and replace all these records with a null() in the load script.

Additionally, what I am looking for is to load the data in a table and averaging the results. However, I do not get the right averages, even though I have made an atempt to clean data in the first place.

I have attached an example, imagine the first load is the excel load.

Report Inappropriate Content · ‎2016-07-04

Ok, I finally got the result:

Set NullInterpret = '-';   

mapNull:
MAPPING LOAD 0 as Key, null() as Value
Autogenerate(1);

MAP * USING mapNull;

View solution in original post

migueldelval · ‎2016-07-04

Hi benchmarking,

Try this example that i´ve made for you, please.

Regards

Miguel del Valle

migueldelval · ‎2016-07-04

Hi benchmarking,

Sorry but I forgot to explain you, that in your texbox "data4" you need to change the expression avg(data4) to avg(Data4).

Remember that QlikView it´s case sensitive.

Regards

Miguel del Valle

Report Inappropriate Content · ‎2016-07-04

Thanks for your help!

Unfortunately this is not what I wanted. I actually do not want zeros, I want nulls instead of zeros and "-" so that for Data3 I get 1 as the average and 2 for Data4.

Sorry that I did not make that clear.

migueldelval · ‎2016-07-04

Hi Benchmarking,

Then use Set analysis: =avg({<Data4={">0"}>}Data4)

Regards

Miguel del Valle

Report Inappropriate Content · ‎2016-07-04

I know this is possible.

However, this is not the solution I am looking for as I have not cleaned data in the first place.

Using the Total Mode "Average" in the Table Properties does not account for set analysis and still zero values would be displayed. This is not what I want, when they would be null() I could set what to be displayed in the table under presentation tab.

migueldelval · ‎2016-07-04

Hi Benchmarking,

Try example other time, please.

In column Data3 you have one possibility and in Data4 other.

Tell me if it´s correct, please

Regards

Miguel del Valle

qliksus · ‎2016-07-04

As per the data sample you have posted simply doing Avg(Data4) would make it display null values

You can now go to presentation tab and choose to display the null value in the way you want

Report Inappropriate Content · ‎2016-07-04

Yes I know figured out the problem of averages.

As I have been using sum, the sum of null is zero which will be accounted for in the total average.

So as migueldelval proposed I will have to check whether the sum is zero or not before acutally summing up.

Also this seems to do the trick to get null values instead of zeros or "-":

if(DataX>0,DataX, null()) as Data1

The only thing I am now still looking for is to check greather than 0 for the whole load and not for any single field.

As I have many many fields in my excel file this would demand great manual effort.

migueldelval · ‎2016-07-04

Hi Benchmarking,

Could you attach asample of your excel?

Regards

Miguel del Valle

Total Averages in Table - Data Clean for Zero and Strings