topic Re: Total Averages in Table - Data Clean for Zero and Strings in QlikView

Total Averages in Table - Data Clean for Zero and Strings

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Total Averages in Table - Data Clean for Zero and Strings

migueldelval — Mon, 04 Jul 2016 08:14:34 GMT

Hi benchmarking,

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

Regards

Miguel del Valle

Re: Total Averages in Table - Data Clean for Zero and Strings

migueldelval — Mon, 04 Jul 2016 08:22:39 GMT

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

Re: Total Averages in Table - Data Clean for Zero and Strings

Mon, 04 Jul 2016 08:34:11 GMT

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.

Re: Total Averages in Table - Data Clean for Zero and Strings

migueldelval — Mon, 04 Jul 2016 08:55:12 GMT

Hi Benchmarking,

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

Regards

Miguel del Valle

Re: Total Averages in Table - Data Clean for Zero and Strings

Mon, 04 Jul 2016 09:01:27 GMT

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.

Re: Total Averages in Table - Data Clean for Zero and Strings

migueldelval — Mon, 04 Jul 2016 09:22:17 GMT

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

Re: Total Averages in Table - Data Clean for Zero and Strings

qliksus — Mon, 04 Jul 2016 09:27:01 GMT

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

Re: Total Averages in Table - Data Clean for Zero and Strings

Mon, 04 Jul 2016 09:33:47 GMT

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.

Re: Total Averages in Table - Data Clean for Zero and Strings

migueldelval — Mon, 04 Jul 2016 09:52:45 GMT

Hi Benchmarking,

Could you attach asample of your excel?

Regards

Miguel del Valle

Re: Total Averages in Table - Data Clean for Zero and Strings

Mon, 04 Jul 2016 10:14:38 GMT

Similar to the Inload 😉

Re: Total Averages in Table - Data Clean for Zero and Strings

Mon, 04 Jul 2016 12:10:54 GMT

Ok, I finally got the result:

Set NullInterpret = '-';   

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

MAP * USING mapNull;