hi friends here is my problem.
my source is hourly feed which has fields like key_off and key_on.
in that feed each vehicle may have multiple records.
the time diff between Key_Off and Key_On is ON_TIME for each vehicle.
here i need to calculate cumulative_on_time for each vehicle .
for example the vehicle A appears first time then cumulative_on_time is 0.
if vehicle A appears second time in the same feed them cumulative time is 0+on_time of current record.
if the vehicle A appears third time then cumulative_on_time= cumulative_on_time of second record+ on_time of current record.
if the same vehicle A appers in the next feed then cumulative_on_time =cumulative_on_time of previous feed+ on_time of current record
pl help me how to do this
how to calculate cumulative_on_time
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
Re: how to calculate cumulative_on_time
I think there are couple of ways of doing it depending on your Target system.
I would use a Transformer to find the TIME_ON for each record and then an aggregator to sum the TIME_ON values (group by vehicle) of the particular feed.
Then I would do a lookup to the target system (i would use a hash file with vehicle as the key and time_on as the attribute) to get the cumilative time_on already existing in the table if any. Add the same to the aggragated value and send the resultant value to target.
Hope it helps.
I would use a Transformer to find the TIME_ON for each record and then an aggregator to sum the TIME_ON values (group by vehicle) of the particular feed.
Then I would do a lookup to the target system (i would use a hash file with vehicle as the key and time_on as the attribute) to get the cumilative time_on already existing in the table if any. Add the same to the aggragated value and send the resultant value to target.
Hope it helps.
Regards
Sonia Jacob
Sonia Jacob
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
hmmm that makes it a little tougher.
So tell me is the following o/p matches ur requirement
Feed 1
Vehicle Name | time_on | cumulative_time_on
A | 1 | 0
A | 1 | 1
A | 1 | 2
A | 1 | 3
Feed 2
Vehicle Name | time_on | cumulative_time_on
A | 1 | 4
A | 1 | 5
A | 1 | 6
A | 1 | 7
Get the max. cumulative from Feed 1, identify these records with a flag = 'P'. Using a link collector put together the max. cumulative from Feed 1 and the data from Feed 2. Sort the data so that all records for a particular vehicle come together with the max. cumulative from Feed 1 as the first record (you could use the timestamp of the records for sorting.)
Send the data to the transformer comparing the vehicle of the current record to that of the previous record. Based on the a successful / unsuccesful comparison the cumulative time_on would be calculated.
if previous record vehicle = current record vehicle then
stagevar = stagevar + current_time_on
send the record to target
else
if previous record vehicle <> current record vehicle and
current record flag = 'P' then
stagevar = cumilative_time_on of cuurent record (max. cumulative from Feed 1)
else
if previous record vehicle <> current record vehicle and
current record flag <> 'P' then
stagevar = 0
send the record to target
Note that i am not sending the any record to the target in the case : if previous record vehicle <> current record vehicle and current record flag = 'P'
hope things are looking better. Are there any other test cases?
So tell me is the following o/p matches ur requirement
Feed 1
Vehicle Name | time_on | cumulative_time_on
A | 1 | 0
A | 1 | 1
A | 1 | 2
A | 1 | 3
Feed 2
Vehicle Name | time_on | cumulative_time_on
A | 1 | 4
A | 1 | 5
A | 1 | 6
A | 1 | 7
Get the max. cumulative from Feed 1, identify these records with a flag = 'P'. Using a link collector put together the max. cumulative from Feed 1 and the data from Feed 2. Sort the data so that all records for a particular vehicle come together with the max. cumulative from Feed 1 as the first record (you could use the timestamp of the records for sorting.)
Send the data to the transformer comparing the vehicle of the current record to that of the previous record. Based on the a successful / unsuccesful comparison the cumulative time_on would be calculated.
if previous record vehicle = current record vehicle then
stagevar = stagevar + current_time_on
send the record to target
else
if previous record vehicle <> current record vehicle and
current record flag = 'P' then
stagevar = cumilative_time_on of cuurent record (max. cumulative from Feed 1)
else
if previous record vehicle <> current record vehicle and
current record flag <> 'P' then
stagevar = 0
send the record to target
Note that i am not sending the any record to the target in the case : if previous record vehicle <> current record vehicle and current record flag = 'P'
hope things are looking better. Are there any other test cases?
Regards
Sonia Jacob
Sonia Jacob
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
Vehicle Name | time_on | cumulative_time_on
A | 1 | 0
A | 1 | 1
A | 1 | 2
A | 1 | 3
oracle 9i stage------
----Link collector------Sort-----Transformer----O/P
Sequential stage----
Oracle 9i : retrieves the max. cumulative of existing vehicle from the table. Also adds a flag "P to each of the distinct vehicle. In the first case there are no such records.
Sequential stage : retrieves the follow.
Vehicle Name | time_on
A | 1
A | 1
A | 1
A | 1
Link Collector: puts together all data.
A | 1
A | 1
A | 1
A | 1
Sort Stage: Sorts the data based on vehicle and timestamp. This will put all the records for a vehicle together and the one from the previos feed first. Once again
A | 1
A | 1
A | 1
A | 1
Transformer stage : To check between the current and the previous record.
there are 2 stage variable
CumTimeOf
PreviousVehicle
if PreviousVehicle = current record vehicle then
CumTimeOf = CumTimeOf + current_time_on
send the record to target
else
if PreviousVehicle <> current record vehicle and
current record flag = 'P' then
CumTimeOf = cumilative_time_on of cuurent record (max. cumulative from Feed 1)
PreviousVehicle = current record vehicle
else
if PreviousVehicle <> current record vehicle and
current record flag <> 'P' then
CumTimeOf = 0
PreviousVehicle = current record vehicle
send the record to target
If you parse the first record through the if cond. given above then it satifies the last if cond. hence ur o/p would be A|1|0 and the PreviousVehicle =A
If you parse the second record through the if cond. given above then it satifies the first if cond. hence ur o/p would be A|1|1
then
A|1|2
A|1|3
Does it sound ok now?
A | 1 | 0
A | 1 | 1
A | 1 | 2
A | 1 | 3
oracle 9i stage------
----Link collector------Sort-----Transformer----O/P
Sequential stage----
Oracle 9i : retrieves the max. cumulative of existing vehicle from the table. Also adds a flag "P to each of the distinct vehicle. In the first case there are no such records.
Sequential stage : retrieves the follow.
Vehicle Name | time_on
A | 1
A | 1
A | 1
A | 1
Link Collector: puts together all data.
A | 1
A | 1
A | 1
A | 1
Sort Stage: Sorts the data based on vehicle and timestamp. This will put all the records for a vehicle together and the one from the previos feed first. Once again
A | 1
A | 1
A | 1
A | 1
Transformer stage : To check between the current and the previous record.
there are 2 stage variable
CumTimeOf
PreviousVehicle
if PreviousVehicle = current record vehicle then
CumTimeOf = CumTimeOf + current_time_on
send the record to target
else
if PreviousVehicle <> current record vehicle and
current record flag = 'P' then
CumTimeOf = cumilative_time_on of cuurent record (max. cumulative from Feed 1)
PreviousVehicle = current record vehicle
else
if PreviousVehicle <> current record vehicle and
current record flag <> 'P' then
CumTimeOf = 0
PreviousVehicle = current record vehicle
send the record to target
If you parse the first record through the if cond. given above then it satifies the last if cond. hence ur o/p would be A|1|0 and the PreviousVehicle =A
If you parse the second record through the if cond. given above then it satifies the first if cond. hence ur o/p would be A|1|1
then
A|1|2
A|1|3
Does it sound ok now?
Regards
Sonia Jacob
Sonia Jacob
look's great but i have small doubts
1)retrieves the max. cumulative of existing vehicle from the table. Also adds a flag P -- how to achive this.
2)reg stage variables CumTimeOf and PreviousVehicle how to set them with current feed values ( am i sounds reasonable).
i am very much thankful for your help.. i will try this on monday and i'll get back to if i have any hurdels.
thanks sonia..
1)retrieves the max. cumulative of existing vehicle from the table. Also adds a flag P -- how to achive this.
2)reg stage variables CumTimeOf and PreviousVehicle how to set them with current feed values ( am i sounds reasonable).
i am very much thankful for your help.. i will try this on monday and i'll get back to if i have any hurdels.
thanks sonia..
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
oracle select stmt. would do the work for u.scottr wrote:look's great but i have small doubts
1)retrieves the max. cumulative of existing vehicle from the table. Also adds a flag P -- how to achive this.
there is no need to worry about the records of the current feed and the previous feed, once it comes to the transformer. The comparison happens between two consecutive records.scottr wrote: 2)reg stage variables CumTimeOf and PreviousVehicle how to set them with current feed values ( am i sounds reasonable).
Regards
Sonia Jacob
Sonia Jacob