topic Re: not loading missings in QlikView

not loading missings

Anonymous — Tue, 21 Apr 2015 14:25:29 GMT

Hi,

When I analyse my data in a hierarchy table, I see the last table row marked '-' with some value.

I suppose that there are empty srrings in my source data and try to exclude missings while loading the data.

I'm trying the following script:

data:

LOAD var_id,

val,

val_name

FROM

Data\VarValues.qvd

(qvd)

where var_id<>' ' and val<>' ' and val_name<>' '

;

But it doesn't help. There are still missings in a table.

Could you have a look?

The file is attached.

Thank you,

Larisa

Re: not loading missings

Agis-Kalogiannis — Tue, 21 Apr 2015 15:41:48 GMT

Hi Larisa

You can right click on the object, go to Properties-> Dimension tab and click the Suppress When Value is Null.

This will make it disappear.

Agis

Re: not loading missings

Agis-Kalogiannis — Tue, 21 Apr 2015 15:48:49 GMT

I see that you are loading from a QVD file, obviously created as a part of a data model.

In order for QlikView to associate values from the dimension tables, that do not exist in your Facts table, it creates Foreign keys in your Facts table, associated with Null values, that's why this line full of nulls is there.

example:

Customers that haven't placed any orders, (exist in the dimension table but with no associated record in the transaction table), Products that have never been sold, etc.

You can follow my above instructions to make it go away.

I hope it is clear. 🙂

Agis

Re: not loading missings

Anonymous — Tue, 21 Apr 2015 16:07:17 GMT

Hi, Agis,

Yes, I know about Suppress When Value is Null.

My task is a bit different. I want to clear my QVD file, I'd like to get rid of empty lines.

My idea was to do it like that:

data:

LOAD var_id,

val,

val_name

FROM

Data\VarValues.qvd

(qvd)

where var_id<>' ' and val<>' ' and val_name<>' '

;

store data into Data\VarValues.qvd(qvd);

DROP Table data;

But it doesn't work. Empty lines (or Foreign keys as you say) still exist.

Do you know how can I get rid of this?

Thank you in advacne,

Larisa

Re: not loading missings

Anonymous — Tue, 21 Apr 2015 16:13:43 GMT

Larisa,

A more reliable way is to use

where len(trim(var_id)>0 and len(trim(val)>0 and len(trim(val_name)>0

But I suspect that nothing's wrong, and you have this "-" because of the nature of the expression in this chart. If you replace 1 with sum(1), it goes away.

Regards,

Michael

Re: not loading missings

Agis-Kalogiannis — Tue, 21 Apr 2015 17:24:21 GMT

OK. Now I understand what you are trying to do.

Here is a sample script

LOAD var_id,

val,

val_name

FROM

Data\VarValues.qvd

(qvd)

Where len(trim(var_id))>0 and len(trim(val))>0 and len(trim(val_name))>0;

Similar to Michael's solutions. Just corrected a couple of missing brackets.

Re: not loading missings

Anonymous — Tue, 21 Apr 2015 17:29:34 GMT

Thanks, everyone!

All your ideas are useful.

Larisa

Re: not loading missings

Anonymous — Tue, 21 Apr 2015 17:30:03 GMT

Thanks Agis, I often mis-count the brackets