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

[narender123]

Hi,

Try this

WHERE

truck3 <> Null();

Regards:

Narender

[giakoum]

no, no attachment is there

[Anonymous]

I added it at the top level in my original post. Sorry

Dan

[Anonymous]

I have tried

truck3 <> Null();

truck3 <> '-';

truck3 <> '';

None of them works

I still get those empty truck records

Dan

[#6]

On your bottom table box, select on key Omit rows then null. Then they will not show

[Anonymous]

That would leave them out of the view yes, but not from the table i self. I would need it to be removed in the load. They should not be included at all.

Dan

[#8]

Hey, just a try

take a text object with expression '=ord(truck2)' hope fully you should get some numeric value by clicking on any of the null values shown in the above screeshot.

if you get '0' then try giving a where clause as 'WHERE truck2<>chr(0)'

lets see what happens....

[Anonymous]

I get the number 49 regardles of what value I click on for the field truck2.

Btw, I then tested WHERE truck2<>chr(49).

No happy ending

Dan

[Sokkorn]

Hi Dan,

Any join table in load script? Anyhow, just let a try: Where truck3 <> Null() or truck3 <> '-' or truck3 <> '' or Len(truck3)>0;

Regards,

Sokkorn

[Anonymous]

Hi Sokkorn

There is a join between the two tables on the Key field.

If I rename hte Key to Key2 and thus break the join your where clause seems to work.

But I then have no connection between the two tables and I cannot get the stopdate field into my main table.

Dan