Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Try this
WHERE
truck3 <> Null();
Regards:
Narender
no, no attachment is there
I added it at the top level in my original post. Sorry
Dan
I have tried
truck3 <> Null();
truck3 <> '-';
truck3 <> '';
None of them works
I still get those empty truck records
Dan
On your bottom table box, select on key Omit rows then null. Then they will not show
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
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....
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
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
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