Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

dan-ketil
Contributor II

Remove null data in load

Hi

I need to remove data where records show null for a spesific field. I cannot do this in the sql query because I need it in yet another load

I have a code lookin like the one below.

I started out with one load, but have read several postes that susggest that I should do a noconcatenate load as well.

In the Stopp load I add a where clause and have tried 3 different methods.

But still my table box from Stopp will show trucks that have null.

Stopp1:

LOAD

truck & '-' & min_lic AS KEY3,

,truck as truck3

.min_lic as min_lic3

.load_time as StoppTime3

,IsNull(truck) as NULLID;

SQL select bla bla.....

NoConcatenate

Stopp:

Load

KEY3 AS KEY,

truck3 as truck,

min_lic3 as min_lic,

StoppTime3 as StoppTime,

NULLID;

RESIDENT Stopp1

WHERE

not IsNull(truck3); // TEST 3

//NULLID;= 0;        // TEST 2   

//len([truck3])>0;  // TEST 1

DROP Table Stopp1:

BR

Dan

23 Replies
giakoum
Honored Contributor II

Re: Remove null data in load

This should be adequate:

WHERE

not IsNull(truck3);

Are you sure the values are null?

Try

WHERE

truck3 <> '';

jonbrough
Valued Contributor

Re: Remove null data in load

I use the Len(FeildName)>0 method and i usually works for me.

TableBox's can be quite misleading as keys common across fields will dupliacte rows and make it look like there are more nulls tha there are.

Try recreating the fields in the datamodel that you want to check on, or using a chart with expressions testing for only(FieldName)

Jonathan

dan-ketil
Contributor II

Re: Remove null data in load

Tried truck <> '' but still I get records that I do not want. The two lines in the midle should not be there.

NULL_ERROR.jpg

Not applicable

Re: Remove null data in load

where nullid is not null

or select supress null values in your straight table.

dan-ketil
Contributor II

Re: Remove null data in load

Looking at the image you see my data. What I need is to rule out the two lines in the left table(pivot) that does not get a stop date and time.

The join is done between KEY and I wnat to display the StoppTime value.

In the pic below you see two records with - instead of a valid timestamp, that is because the flow have been stopped for some reason and thus I do not want to import them to my stop table at all, for this view.

NULL_ERROR_2.jpg

Not applicable

Re: Remove null data in load

try to promote or  demote your dimension, then in dimension select field and check supress null value.

I think if you take Key in expression then use supress null value in dimension.

kumarnatarajan
Valued Contributor

Re: Remove null data in load

Hi,

Some Records have Space so use below mentioned Condition.

EX:

WHERE Len(Trim(truck3))>0;

giakoum
Honored Contributor II

Re: Remove null data in load

Please give some sample data, and it will be fixed in no time.

Just make sure you give the source also, so that we can reload.

dan-ketil
Contributor II

Re: Remove null data in load

Hi

I have not attached code examples before, but tried it no. Hope I have done it correctly

Dan

Community Browser