Using Raw Data: No Value referenced with symbol

[#1]

Hi,

I have got some raw data in Excel looking like:

Data1	Data2
1	-
-	8
11	-
7	8
-	1

So if there is no value, I do have a "-" in the cell.

However, I would like to leave the data untouched.

How can I define that these cells are skipped when calculating stuff like averages or simple summation in Qlikview?

Many thanks in advance

[#2]

While load the data into Qlikview, you may clean the data somehow on number fields. Otherwise it may cause some calculation issues.

So while loading, insert 0 if you found null.

LOAD

     A, Alt(Data1,0) as Data1, Alt(Data2) as Data2

From excel_file;

Sum( Data1)

avg({Data2={">0"}} Data2)

[#3]

Companyname	Year	Data1	Data2
A	2016	1	-
A	2015	-	8
B	2016	11	-
B	2015	7	8
C	2016	-	1
C	2016	7	-

So this is what my excel file I am loading in Qlik looks like.

In the Load-Script I have defined Rangesum(Data1,Data2) as Data3.

Having Year and Companyname as dimension in a table I want the following result when choosing Company A in the Listbox:

	2016	2015
Data3	1	8

@swuehl

"Sum() and Avg() functions don't seem to worry about your '-' values" does not work for me, I don´t get any value.

[#4]

I don´t have any problem loading data like this in case my calculations don´t cause any troubles, I will try your avg example.

Anyhow is there a way of loading the whole excel file like you proposed?

[swuehl]

Have you checked that after your LOAD, you get Data3 filled in every record, getting the sum of the numeric values of Data1 and Data2 for each input record?

If this is the case, create a straight table chart with dimension Year and as expression

=Sum(Data3)

[Remember to use aggregation functions in chart expressions!]

Label this expression 'Data3'.

Choose 'Horizontal' on presentation tab.

Select 'Company A'.

[sunny_talwar · Accepted solution]

Seems to be working, what is the issue?

Script:

Table:

LOAD Companyname,

    Year,

    Data1,

    Data2,

    RangeSum(Data1, Data2) as Data3

FROM

[https://community.qlik.com/thread/211251]

(html, codepage is 1252, embedded labels, table is @6);

[#7]

Yes you can add the Alt or IF function to insert default value if there is a null value in source.

[#8]

I finally got your stuff going in my document.

The problem was that I have not defined anything in ONE Load Script but in different tables like:

Data:

LOAD Companyname,

    Year,

    Data1,

    Data2,

FROM

[https://community.qlik.com/thread/211251]

(html, codepage is 1252, embedded labels, table is @6);

KPI:

LOAD

    RangeSum(Data1, Data2) as Data3

FROM

[https://community.qlik.com/thread/211251]

(html, codepage is 1252, embedded labels, table is @6);

What is the issue? Any chance I can split up my data like this?

[swuehl]

Well, maybe you can describe what you want to achieve in the long run, i.e. what you want to analyze.

Sure you can create a data island only showing Data3 field with the rangesum() of Data1 and Data2, but for what purpose?

Could you please try once to describe what you want show in the front end and why the solutions already suggested didn't work for you?

[sunny_talwar]

Try this:

Data:

LOAD Companyname,

    Year,

    Data1,

    Data2,

FROM

[https://community.qlik.com/thread/211251]

(html, codepage is 1252, embedded labels, table is @6);

KPI:

LOAD Companyname,

        Year,

        RangeSum(Data1, Data2) as Data3

Resident Data;

[swuehl]

Or

Data:

LOAD Companyname,

         Year,

         RangeSum(Data1, Data2) as Data3;

LOAD Companyname,

    Year,

    Data1,

    Data2,

FROM

[https://community.qlik.com/thread/211251]

(html, codepage is 1252, embedded labels, table is @6);

or any other of two billion possible load statements...?