Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Partner - Master II
Partner - Master II

This should be adequate:

WHERE

not IsNull(truck3);

Are you sure the values are null?

Try

WHERE

truck3 <> '';

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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
Author

where nullid is not null

or select supress null values in your straight table.

Anonymous
Not applicable
Author

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
Author

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
Partner - Specialist
Partner - Specialist

Hi,

Some Records have Space so use below mentioned Condition.

EX:

WHERE Len(Trim(truck3))>0;

giakoum
Partner - Master II
Partner - Master II

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.

Anonymous
Not applicable
Author

Hi

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

Dan