Remove null data in load

[Anonymous]

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

[giakoum]

This should be adequate:

WHERE

not IsNull(truck3);

Are you sure the values are null?

Try

WHERE

truck3 <> '';

[Anonymous]

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]

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

[#5]

where nullid is not null

or select supress null values in your straight table.

[Anonymous]

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.

[#7]

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]

Hi,

Some Records have Space so use below mentioned Condition.

EX:

WHERE Len(Trim(truck3))>0;

[giakoum]

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]

Hi

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

Dan