Can QlikView regard Null as any other value?

[Anonymous]

Hi,

E.g. my source data is like this:

Media	Brand	Cost
Radio	AUDI	10
Press	AUDI	20
TV	AUDI	12
Radio	BENTLEY	15

Task 1: While loading this data to QlikView I want to transform this data in the following way. I hope it will help me to solve task 2. How is that possible?

Media	Brand	Cost
Radio	AUDI	10
Press	AUDI	20
TV	AUDI	12
Radio	BENTLEY	15
Press	BENTLEY	0
TV	BENTLEY	0

Task 2: My task is to regard Null as a real value while selecting from a check-box and creating chart on the forefront. Is it possible in QlikView? Besides, is the fulfilment of task 1 necessary?

Thank you in advance,

Larisa

[oknotsen]

Note:

When you have a null value, that usually means that the value is unknown. Be aware that unknown and 0 are different so only replace the one by the other if you are 105% sure you will not get into trouble later down the road.

What you could do is something like this in your load script:

LOAD

if (NULL(SomeField), 0, SomeField) as SomeField,

...

[sunny_talwar]

To add to this, any averages you will calculate will also be impacted by 0's (whereas null doesn't impact averages). for example average of:

10,2,0 = 4, but average of 10,2, null = 6

Best,

Sunny

[sunny_talwar]

To handle Null, you might find this document to be useful: NULL handling in QlikView‌ (page 12-13 can be useful) in your case.

HTH

Best,

Sunny

[Anonymous]

Thanks, everyone!

I'll look into it.

[maxgro · Accepted solution]

for task 1

a: load * inline [

Media, Brand, Cost

Radio, AUDI, 10

Press, AUDI, 20

TV, AUDI, 12

Radio, BENTLEY, 15

];

b: load Distinct Media Resident a;

join (b) load Distinct Brand Resident a;

Right Join (a) load * Resident b;

Left Join (a) load Media, Brand, alt(Cost, 0) as Cost1 Resident a;

DROP Table b;

DROP Field Cost;

RENAME Field Cost1 to Cost; 

[Anonymous]

Hi, Onno, thanks for the approach which you suggested for loading Nulls, but my task is more complicated.

I’m using the following script:

data:
LOAD Media,
Brand,
Cost
FROM
C:\Users\Larisa.Filonova\Desktop\Table2.xlsx
(ooxml, embedded labels, table is Sheet1);

Then I get the following table in QlikView:

Media	Brand	Cost
Radio	Brand1	10
Press	Brand1	20
TV	Brand1	12
Radio	Brand2	15

Now my task is to make QlikView create two more strings in a table and set Cost = 0 for these strings.

Press	Brand2	0
TV	Brand2	0

Simply speaking, I want to set a condition in my script: if a Brand is NOT defined for some of the media, then create an extra string in a table for the missing Media and define Cost = 0. How is it possible?

[Anonymous]

Thank you! It works.