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 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
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)
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
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