Talend community forum / IN need of help to implement a logic

arasu08 · 2012-06-15 07:50:56

Tags: [database, date, development, mysql]

Hi Every one

I have a TABLE AS market_summary which CONTAINS LIST of shares AND their prices IN particular DATE AS follows

DATE SHARE_NAME    COST

2012-06-08 APEC 120

2012-06-09 APEC 140

2012-06-12 APEC 110

2012-06-12    AMEC 105

2012-06-14 AMEC 110

AND I want the output TABLE AS

DATE SHARE_NAME COST

2012-06-08    APEC 120

2012-06-09    APEC 140

2012-06-10    APEC 140
2012-06-11    APEC 140

2012-06-12    APEC 110

2012-06-12    AMEC 105

2012-06-13    AMEC 105

2012-06-14 AMEC 110

so that if a share doesnt contains value for a particular day then i want one entry for that day with previous existing value. Please help me with job design

Thanks and regards

S.Arasu

pedro · 2012-06-15 08:48:00

Hi

I create a job here.

Code in tJavaRow

Code:

//Code generated according to input schema and output schema
if(Share_tMemorizeRows_1[1]==null){
}else if((Share_tMemorizeRows_1[1].equals(Share_tMemorizeRows_1[0]))&&(TalendDate.diffDate(Date_tMemorizeRows_1[0],Date_tMemorizeRows_1[1])>1)){

context.date = Date_tMemorizeRows_1[1];
context.share = Share_tMemorizeRows_1[1];
context.cost = Cost_tMemorizeRows_1[1];
context.differ = (int)TalendDate.diffDate(Date_tMemorizeRows_1[0],Date_tMemorizeRows_1[1])-1;
context.flag=true;

}else{
context.flag=false;
}

Code in tFixFlowInput

Code:

TalendDate.addDate(context.date,((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")),"dd")

Regards,
Pedro

Uploaded Images

saburo · 2012-06-15 08:51:19

Hello,
this is a typical issue in BI (and other domains).
We normally solve it by creating a dataset (table, view...) with the list of the days (aka a time dimension table) and left joining it with the SHARE dimension.
At that point you have all the combinations day / share and you can jopin it with the transactional table
with a subquery thatgets the price for the maximum day which is less or equal to the desired day.

This works, but jonis and groupings can be a bit heavy, so the method can be replacd with a stored procedure (done this in MS SQL and Oracle a lot).
What you wanto to do with that one is to get the transactional table sorted by "normal" key first (SHARE) and time key after (day).
You then scan and use a variable to keep the last value.
If a day is missing (detetc the gap between two consecutive days) you output all the intermediate records using the last value stored in your variable.
IF accomplished on the DB and you are populating a target table, this is the fastest method.

A slight variation you could implement in Talend is to left join a time dimension table (i.e. you can use my component http://www.talendforge.org/exchange/ind … ;nav=2,1,1 or similar ones) .
Make sure you carefully specify the desired time range.

Your input table should be sorted by share, time.

After the join just put a tjavaFlex, in the begin section you will define a variable "lastValue" and another one "lastShare"
Init them to -1 and "ZZZZ"

In the main section compare row.share with lastShare, if they are different :
lastShare = row.share;
lastValue = -1;
else
if ((row.value==null)&&(lastValue>-1)) row.value = lastValue;
if (row.value!=null) lastValue = row.value;

You could build a component, but the tasks "sort" , "left join", "generate time dimension" are already solved by existing compoennts, so it would make sense to use them.

Hope it helps

arasu08 · 2012-06-15 10:09:20

Thanks for Your timely help

Thanks and regards
S.Arasu

Announcement

Unanswered posts

#1 2012-06-15 07:50:56

IN need of help to implement a logic

#2 2012-06-15 08:48:00

Re: IN need of help to implement a logic

Code:

Code:

#3 2012-06-15 08:51:19

Re: IN need of help to implement a logic

#4 2012-06-15 10:09:20

Re: IN need of help to implement a logic

Board footer