not loading missings

[Anonymous]

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

[Agis-Kalogiannis]

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

[Agis-Kalogiannis]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Agis-Kalogiannis]

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.

[Anonymous]

Thanks, everyone!

All your ideas are useful.

Larisa

[Anonymous]

Thanks Agis, I often mis-count the brackets