Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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,
...
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
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
Thanks, everyone!
I'll look into it.
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;
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?
Thank you! It works.