topic Re: Using Raw Data: No Value referenced with symbol in QlikView

Using Raw Data: No Value referenced with symbol

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Using Raw Data: No Value referenced with symbol

sunny_talwar — Tue, 29 Mar 2016 14:02:10 GMT

May be this:

Avg({<Data1 -= {'-'}>}Data1)

Note the small negative sign in red before the equal sign

Re: Using Raw Data: No Value referenced with symbol

swuehl — Tue, 29 Mar 2016 14:25:15 GMT

So these records show a character '-'? Or is this your representation of NULL?

You probably won't need to do anything.

Sum() will only sum up numeric values. Also Avg() should only operate on the numeric values.

Re: Using Raw Data: No Value referenced with symbol

Tue, 29 Mar 2016 14:26:04 GMT

Thanks, seems to work.

Can I define this overall in the script?

Did some x+z as y loading in the script, which doesn´t work with the "-" values...

Re: Using Raw Data: No Value referenced with symbol

Tue, 29 Mar 2016 14:29:57 GMT

See above, that doesn´t work with predefined formulas.

Re: Using Raw Data: No Value referenced with symbol

sunny_talwar — Tue, 29 Mar 2016 16:33:50 GMT

Not sure I understand, can you provide a sample of what you are doing?

Re: Using Raw Data: No Value referenced with symbol

swuehl — Tue, 29 Mar 2016 16:42:48 GMT

Try

Rangesum(x,z) as y

Re: Using Raw Data: No Value referenced with symbol

Tue, 05 Apr 2016 14:33:28 GMT

Companyname	Data1	Data2
A	1	-
A	-	8
B	11	-
B	7	8
C	-	1
C	7	-

So in the loadscript I have defined

Rangesum(Data1,Data2) as Data3

Depending on the chosen Company I want to illustrate the result in a table.

Using SUM({<Companyname>}[Data3]) does not get me the right result.

What is the right function?

Re: Using Raw Data: No Value referenced with symbol

sunny_talwar — Tue, 05 Apr 2016 16:23:21 GMT

What is the number you are expecting to see from the above sample?

Re: Using Raw Data: No Value referenced with symbol

swuehl — Tue, 05 Apr 2016 23:11:16 GMT

Is this what you get when executing the LOAD?

Companyname	Data1	Data2	Data3
A	-	8	8
A	1	-	1
B	7	8	15
B	11	-	11
C	-	1	1
C	7	-	7

This is when I use Companyname as dimension in a chart and

SUM({<Companyname>}[Data3])

as expression:

Companyname	SUM({<Companyname>}[Data3])
	43
A	9
B	26
C	8

Seems ok to me, or please advise what you expect instead.

Also, as I mentioned above, Sum() and Avg() functions don't seem to worry about your '-' values:

Companyname	SUM({<Companyname>}[Data3])	Sum(Data1)	Avg(Data1)
	43	26	6,5
A	9	1	1
B	26	18	9
C	8	7	7

Re: Using Raw Data: No Value referenced with symbol

Wed, 06 Apr 2016 03:12:08 GMT

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)

Re: Using Raw Data: No Value referenced with symbol

Wed, 06 Apr 2016 06:49:01 GMT

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.

Re: Using Raw Data: No Value referenced with symbol

Wed, 06 Apr 2016 07:00:00 GMT

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?

Re: Using Raw Data: No Value referenced with symbol

swuehl — Wed, 06 Apr 2016 07:24:13 GMT

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'.

Re: Using Raw Data: No Value referenced with symbol

sunny_talwar — Wed, 06 Apr 2016 09:05:57 GMT

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);

Re: Using Raw Data: No Value referenced with symbol

Wed, 06 Apr 2016 12:29:28 GMT

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

Re: Using Raw Data: No Value referenced with symbol

Wed, 06 Apr 2016 14:55:29 GMT

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?

Re: Using Raw Data: No Value referenced with symbol

swuehl — Wed, 06 Apr 2016 15:27:12 GMT

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?

Re: Using Raw Data: No Value referenced with symbol

sunny_talwar — Wed, 06 Apr 2016 15:28:26 GMT

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;

Re: Using Raw Data: No Value referenced with symbol

swuehl — Wed, 06 Apr 2016 15:32:03 GMT

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...?