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

[Sokkorn]

Hi Dan,

Can you explain me a little bit about your table structure?

Ex:

[Table1]:

     Key,

     Field1,

.........

[Table2]:

     Key,

     StartDate,

.........

Where is your stopdate field?

Now I'm thinking about Mapping Load that can be do this job.

Regards,

Sokkorn

[Anonymous]

I will try the best I can.

Please find in my original post a qvw file as an example.

In "short" I originaly started with:

I first select all trucks and load id and start date in

Start:

LOAD

......,

truck & '-' & min_lic AS KEY, // this is the key

truck as truck1, // truck id

min_lic as min_lic1, // load id

lasta_tid as StartTid // Starttime for the loadid

,NoRC as AntallRC; // number of colli on the load id

SQL QUERY....

Then I have created a reverse SQL to get the stop stamp

Stopp:

Load

KEY3 AS KEY,

truck3 as truck4, // I do not need this, I just get it to check the key

min_lic3 as min_lic4, // I do not need this, I just get it to check the key

StoppTid3 as StoppTid;

I then want to create a pivot table listing the truck, loadid, start and stop and the time between the two.

The problem is that the stop return some null values and those I do not want. When the loading of stopp runs I need it to remove loadid where stopptid is Null.(the truck field would be null at the same time thus I have been checking on the truck field)

[Sokkorn]

Hi Dan,

Can you try this procedure

Stopp:

Mapping

Load

    KEY3 AS KEY,

    StoppTid3 as StoppTid;

From Table;

Start:

LOAD

    ......,

    truck & '-' & min_lic AS KEY, // this is the key

    AppkyMap('Stopp',truck & '-' & min_lic,'TBC')    AS [Stopp],

    truck as truck1, // truck id

    min_lic as min_lic1, // load id

    lasta_tid as StartTid // Starttime for the loadid

    ,NoRC as AntallRC; // number of colli on the load id

SQL QUERY....

==============================

Make sure [Stopp] is load on top of [Start].

Regards,

Sokkorn

[Anonymous]

Sokkorn.

Sorry for late reply. Tried your solution, but I am not familiar with applymap(reading about it as we speek)

Here is my new code(it still does not give the right output, I know get no data into the StoppTime field):

Stopp:

MAPPING

LOAD

truck & '-' & min_lic AS KEY,

load_time as StoppTime

Where truck <> Null() or truck <> '-' or truck <> '' or Len(truck)>0;

SQL select

        from_location  as truck

        ,min("From license number.")  as min_lic   

        ,"Date record was created."   as  load_time

from

     trans

where  ----;

Start:

LOAD

AppLyMap('Stopp',truck & '-' & min_lic,'TBC')    AS [Stopp],

truck & '-' & min_lic AS KEY,

truck as truck1,

min_lic as min_lic1,

lasta_tid as StartTime,

NoRC as RC;

SQL select

        to_location  as truck

        ,min("From license number.")  as min_lic

        ,"Date record was created."   as  lasta_tid

        ,count(distinct "From license number.") as NoRC

from

    trans

where ----;

I think I might need a group bu clause as weel in the Stopp part, since I can get several timstamps with just seconds apart.

So what I need is for each key to get the first(min) timestamp for stop.

BR

Dan